from rest_framework.views import APIView
from rest_framework.response import Response
from rest_framework import status
from rest_framework.viewsets import ModelViewSet
from elasticsearch import Elasticsearch
from django.http import StreamingHttpResponse
from django.utils.encoding import escape_uri_path

from equity_mall.utils.pages import MyPage
from equity_admin.serializers_orders import *
from equity_mall.utils.utils import get_level_list, ryx_profitsharing_order, get_sign
from customers.utils import AlipayOpen as C_AlipayOpen
from equity_admin.tasks import *


class OrderInfoModelViewSet(ModelViewSet):
    '''订单'''

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    # search_fields = ('merchant__name', 'ordergoods__goods__name', 'customer_user__mobile')
    filter_class = OrderInfoFilter

    def get_serializer_class(self):
        if self.action == 'list':
            return OrderInfoListModelSerializer
        else:
            return OrderInfoRetrieveModelSerializer

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        search = self.request.query_params.get('search', None)
        customeruser_mobile = self.request.query_params.get('customeruser_mobile', None)
        create_time_end = self.request.query_params.get('create_time_end', None)
        payment_time_end = self.request.query_params.get('payment_time_end', None)
        use_time_end = self.request.query_params.get('use_time_end', None)
        try:
            if '_mutable' in self.request.query_params.__dict__:
                self.request.query_params.__dict__['_mutable'] = True
        except:
            pass
        if create_time_end:
            self.request.query_params['create_time_end'] = f'{create_time_end} 23:59:59'
        if payment_time_end:
            self.request.query_params['payment_time_end'] = f'{payment_time_end} 23:59:59'
        if use_time_end:
            self.request.query_params['use_time_end'] = f'{use_time_end} 23:59:59'
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = OrderInfo.objects.all()
        elif level.level == 1:
            queryset = OrderInfo.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = OrderInfo.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = OrderInfo.objects.filter(level__district_id=level.id)
        else:
            queryset = OrderInfo.objects.filter(level__branch_id=level.id)
        if customeruser_mobile:
            customeruservice_id = CustomerUserVice.objects.filter(mobile=customeruser_mobile).values_list('id', flat=True)
            if customeruservice_id:
                queryset = queryset.filter(customeruservice_id__in=list(customeruservice_id))
            else:
                queryset = queryset.filter(id=1)
        if search:
            es = Elasticsearch(["http://159.75.55.19:9200/"])
            body = {
                "query": {
                    "bool": {
                        "must": [],
                        "must_not": [],
                        "should": []
                    }
                },
                "from": 0,
                "size": 10000,
                "sort": [{"_id": "asc"}],
                "aggs": {}
            }
            if not search.isdigit():
                filter_data = {
                    "match_phrase_prefix": {
                        "text": search
                    }
                }
                body['query']['bool']['must'].append(filter_data)
            else:
                filter_data = {
                    "wildcard": {
                        "text": f'*{search}*'
                    }
                }
                body['query']['bool']['must'].append(filter_data)
            body['query']['bool']['must_not'].append({"wildcard": {"text": "*vm*"}})
            result = es.search(index="equity_mall_order", doc_type="modelresult", body=body)
            # print(result)
            hits = result['hits']['hits']
            id_list = []
            for order in hits:
                id_list.append(order['_id'])
            queryset = queryset.filter(id__in=id_list)
        return queryset.order_by('-create_time')

    def export_orderinfo(self, request):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        create_time_begin = self.request.query_params.get('create_time_begin', None)
        create_time_end = self.request.query_params.get('create_time_end', None)
        payment_time_begin = self.request.query_params.get('payment_time_begin', None)
        payment_time_end = self.request.query_params.get('payment_time_end', None)
        use_time_begin = self.request.query_params.get('use_time_begin', None)
        use_time_end = self.request.query_params.get('use_time_end', None)
        total_price_min = self.request.query_params.get('total_price_min', None)
        total_price_max = self.request.query_params.get('total_price_max', None)
        merchant_id = self.request.query_params.get('merchant_id', None)
        customeruser_id = self.request.query_params.get('customeruser_id', None)
        state = self.request.query_params.get('state', None)
        search = self.request.query_params.get('search', None)
        order_species = self.request.query_params.get('order_species', None)
        goods_id = self.request.query_params.get('goods_id', None)
        customeruser_mobile = self.request.query_params.get('customeruser_mobile', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE id!=1'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        if search:
            es = Elasticsearch(["http://159.75.55.19:9200/"])
            body = {
                "query": {
                    "bool": {
                        "must": [],
                        "must_not": [],
                        "should": []
                    }
                },
                "from": 0,
                "size": 10000,
                "sort": [],
                "aggs": {}
            }
            if not search.isdigit():
                filter_data = {
                    "match_phrase_prefix": {
                        "text": search
                    }
                }
                body['query']['bool']['must'].append(filter_data)
            else:
                filter_data = {
                    "wildcard": {
                        "text": f'*{search}*'
                    }
                }
                body['query']['bool']['must'].append(filter_data)
            body['query']['bool']['must_not'].append({"wildcard": {"text": "*vm*"}})
            result = es.search(index="equity_mall_order", doc_type="modelresult", body=body)
            # return Response(result)
            hits = result['hits']['hits']
            id_list = []
            for order in hits:
                id_list.append(order['_id'])
            if id_list:
                if len(id_list) == 1:
                    condition = condition + f' AND id IN {tuple(id_list)}'.replace(',', '')
                else:
                    condition = condition + f' AND id IN {tuple(id_list)}'
            # condition = condition + f' AND (id in (SELECT order_id FROM tb_order_goods WHERE merchant_id IN (SELECT id FROM tb_merchant WHERE name LIKE "%{search}%") OR goods_id IN (SELECT id FROM tb_goods WHERE name LIKE "%{search}%") OR customer_user_id IN (SELECT id FROM tb_u_customer_user WHERE mobile LIKE "%{search}%")))'
        if create_time_begin:
            condition = condition + f' AND create_time >= "{create_time_begin + " 00:00:00"}"'
        if create_time_end:
            condition = condition + f' AND create_time <= "{create_time_end + " 23:59:59"}"'
        if payment_time_begin:
            condition = condition + f' AND payment_time >= "{payment_time_begin + " 00:00:00"}"'
        if payment_time_end:
            condition = condition + f' AND payment_time <= "{payment_time_end + " 23:59:59"}"'
        if use_time_begin:
            condition = condition + f' AND use_time >= "{use_time_begin + " 00:00:00"}"'
        if use_time_end:
            condition = condition + f' AND use_time <= "{use_time_end + " 23:59:59"}"'
        if total_price_min:
            condition = condition + f' AND total_price >= "{total_price_min}"'
        if total_price_max:
            condition = condition + f' AND total_price <= "{total_price_max}"'
        if state:
            condition = condition + f' AND state in ({state})'
        if order_species:
            condition = condition + f' AND order_species in ({order_species})'
        if merchant_id:
            condition = condition + f' AND merchant_id={merchant_id}'
        if customeruser_id:
            condition = condition + f' AND customer_user_id={customeruser_id}'
        if goods_id:
            condition = condition + f' AND id IN (SELECT order_id FROM tb_order_goods WHERE goods_id={goods_id})'
        if customeruser_mobile:
            condition = condition + f' AND customeruservice_id IN (SELECT id FROM tb_u_customer_user_vice WHERE mobile="{customeruser_mobile}")'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT t.*,u.address_mobile,receiver,province,city,district,place FROM (SELECT r.*,s.vice_mobile,vice_realname FROM (SELECT p.*,q.merhant_acount,merchant_city_name,merhant_district_name,merchant_branch_name,merchant_name,wyfMerchantID FROM (SELECT l.*,m.goods_name,goods_count,goods_price FROM (SELECT g.*,h.coupons_id,coupons_status,coupons_use_time FROM (SELECT c.*,d.customer_level,mobile,realname FROM (SELECT a.*, b.order_city_name,order_district_name,order_branch_name,order_account FROM (SELECT id,level_id,state,total_price,total_point,(total_price-realpay_amount)AS coupons_amount,realpay_amount,customer_user_id,orderNum,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S') AS create_time,DATE_FORMAT(payment_time,'%Y-%m-%d %H:%i:%S') AS payment_time,merchant_id,order_id,trade_no,customeruservice_id,ownership FROM tb_order_info {condition})AS a LEFT JOIN (SELECT id,city_name AS order_city_name,district_name AS order_district_name,branch_name AS order_branch_name,account AS order_account FROM tb_u_level)AS b ON a.level_id=b.id)AS c LEFT JOIN (SELECT e.*,f.customer_level FROM (SELECT id,mobile,realname,level_id FROM tb_u_customer_user)AS e LEFT JOIN (SELECT id,CONCAT(prov_name,'-',city_name,'-',district_name,'-',branch_name)AS customer_level FROM tb_u_level)AS f ON e.level_id=f.id)AS d ON c.customer_user_id=d.id)AS g LEFT JOIN (SELECT GROUP_CONCAT(coupons_id)AS coupons_id,GROUP_CONCAT(status)AS coupons_status,GROUP_CONCAT(DATE_FORMAT(use_time,'%Y-%m-%d %H:%i:%S'))AS coupons_use_time,order_id FROM tb_goods_coupons WHERE order_id IN (SELECT id FROM tb_order_info {condition}) GROUP BY order_id)AS h ON g.id=h.order_id)AS l LEFT JOIN (SELECT GROUP_CONCAT(k.name)AS goods_name,GROUP_CONCAT(k.count)AS goods_count,GROUP_CONCAT(k.price)AS goods_price,order_id FROM (SELECT i.price,count,order_id,j.name FROM (SELECT * FROM tb_order_goods WHERE order_id IN (SELECT id FROM tb_order_info {condition}))AS i LEFT JOIN (SELECT * FROM tb_goods)AS j ON i.goods_id=j.id)AS k GROUP BY order_id)AS m ON l.id=m.order_id)AS p LEFT JOIN (SELECT n.*,o.merhant_acount,merchant_city_name,merhant_district_name,merchant_branch_name FROM (SELECT id,name AS merchant_name,wyfMerchantID,level_id FROM tb_merchant)AS n LEFT JOIN (SELECT id,city_name AS merchant_city_name,district_name AS merhant_district_name,branch_name AS merchant_branch_name,account AS merhant_acount FROM tb_u_level)AS o ON n.level_id=o.id)AS q ON p.merchant_id=q.id)AS r LEFT JOIN (SELECT id,mobile AS vice_mobile,realname AS vice_realname FROM tb_u_customer_user_vice WHERE id IN (SELECT customeruservice_id FROM tb_order_info {condition}))AS s ON r.customeruservice_id=s.id)AS t LEFT JOIN (SELECT receiver,province,city,district,place,mobile AS address_mobile,order_id FROM tb_order_address WHERE order_id IN (SELECT id FROM tb_order_info {condition}))AS u ON t.id=u.order_id;
            '''
            cursor.execute(sql)
        except Exception as e:
            sql = f'''
            SELECT p.*,q.account,level_name,merchant_name,wyfMerchantID FROM (SELECT l.*,m.goods_name,goods_count,goods_price FROM (SELECT g.*,h.coupons_id,coupons_status,coupons_use_time FROM (SELECT c.*,d.customer_level,mobile,realname FROM (SELECT a.*, b.name FROM (SELECT id,level_id,state,total_price,total_point,(total_price-realpay_amount)AS coupons_amount,realpay_amount,customer_user_id,orderNum,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S') AS create_time,merchant_id,order_id,trade_no FROM tb_order_info WHERE id=1)AS a LEFT JOIN (SELECT id,name FROM tb_u_level)AS b ON a.level_id=b.id)AS c LEFT JOIN (SELECT e.*,f.customer_level FROM (SELECT id,mobile,realname,level_id FROM tb_u_customer_user)AS e LEFT JOIN (SELECT id,CONCAT(prov_name,'-',city_name,'-',district_name,'-',branch_name)AS customer_level FROM tb_u_level)AS f ON e.level_id=f.id)AS d ON c.customer_user_id=d.id)AS g LEFT JOIN (SELECT GROUP_CONCAT(coupons_id)AS coupons_id,GROUP_CONCAT(status)AS coupons_status,GROUP_CONCAT(DATE_FORMAT(use_time,'%Y-%m-%d %H:%i:%S'))AS coupons_use_time,order_id FROM tb_goods_coupons GROUP BY order_id)AS h ON g.id=h.order_id)AS l LEFT JOIN (SELECT GROUP_CONCAT(k.name)AS goods_name,GROUP_CONCAT(k.count)AS goods_count,GROUP_CONCAT(k.price)AS goods_price,order_id FROM (SELECT i.price,count,order_id,j.name FROM (SELECT * FROM tb_order_goods)AS i LEFT JOIN (SELECT * FROM tb_goods)AS j ON i.goods_id=j.id)AS k GROUP BY order_id)AS m ON l.id=m.order_id)AS p LEFT JOIN (SELECT n.*,o.account,level_name FROM (SELECT id,name AS merchant_name,wyfMerchantID,level_id FROM tb_merchant)AS n LEFT JOIN (SELECT id,name AS level_name,account FROM tb_u_level)AS o ON n.level_id=o.id)AS q ON p.merchant_id=q.id;
            '''
            cursor.execute(sql)
        ziduan = ['id', '平台订单号', '订单号', '支付流水号', '下单时间', '商品名称', '单价', '数量', '总金额(元)', '商家信息', '微邮付商户号', '商家所属市公司', '商家所属区公司', '商家所属网点', '商家所属机构号', '购买市公司', '购买区公司', '购买网点', '购买机构号', '订单状态', '付款时间', '邮米抵扣', '优惠券抵扣(元)', '实付金额(元)', '券码', '券码状态', '券码核销时间', '购买用户手机号', '用户真实姓名', '用户归属', '收货人', '收货人手机号', '收货地址', '小邮美宿标识']
        cols = ['id', 'order_id', 'orderNum', 'trade_no', 'create_time', 'goods_name', 'goods_price', 'goods_count', 'total_price', 'merchant_name', 'wyfMerchantID', 'merchant_city_name', 'merhant_district_name', 'merchant_branch_name', 'merhant_acount', 'order_city_name', 'order_district_name', 'order_branch_name', 'order_account', 'state', 'payment_time', 'total_point', 'coupons_amount', 'realpay_amount', 'coupons_id', 'coupons_status', 'coupons_use_time', 'mobile', 'realname', 'customer_level', 'receiver', 'address_mobile', 'place', 'ownership']
        response = StreamingHttpResponse(self.get_orderinfo_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('订单列表.csv'))
        return response

    def get_orderinfo_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        STATUS_ENUM = {
            0: '待付款',
            1: '待付款',
            2: '待核销',
            3: '已核销',
            4: '订单过期',
            5: '售后中',
            6: '已发货',
            7: '已预约',
            8: '已退款',
            9: '发货中',
            10: '退款中',
            11: '订单异常',
            12: '已付款',
            13: '已取消',
            14: '已完成',
            15: '待成团'
        }
        COUPONS_STATUS_ENUM = {
            "0": '未激活',
            "1": '待核销',
            "2": '已核销',
            "3": '已过期',
            "4": '已撤销'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'state':
                    tmp_str += '"%s",' % (STATUS_ENUM[row[col]])
                elif col == 'coupons_status':
                    coupons_status_str = ''
                    if row[col]:
                        coupons_status_list = row[col].split(',')
                        for coupons_status in coupons_status_list:
                            coupons_status_str += COUPONS_STATUS_ENUM[coupons_status]
                            coupons_status_str += ','
                    tmp_str += '"%s",' % (coupons_status_str)
                elif col in ['mobile', 'realname']:
                    if row['customeruservice_id']:
                        tmp_str += '"%s",' % (str(row[f'vice_{col}']).encode('GBK', 'ignore').decode('gbk') if row[f'vice_{col}'] else '')
                    else:
                        tmp_str += '"%s",' % (str(row[f'{col}']).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                elif col == 'place':
                    tmp_str += '"%s",' % (str(f'{row["province"]}-{row["city"]}-{row["district"]}-{row[col]}').encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                elif col in ['orderNum', 'trade_no', 'coupons_id', 'order_id']:
                    tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                elif col in ['total_price', 'total_point', 'coupons_amount', 'realpay_amount']:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else 0)
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"

    def destroy(self, request, *args, **kwargs):
        instance = self.get_object()
        if instance.state != 1:
            return Response({'error': '订单状态错误'}, status=status.HTTP_400_BAD_REQUEST)
        ordergoods_set = instance.ordergoods_set.all()
        for ordergoods in ordergoods_set:
            goods = ordergoods.goods
            launched_goods = ordergoods.launched_goods
            count = ordergoods.count
            origin_sales = goods.sales
            origin_remain_stock = goods.remain_stock
            new_sales = origin_sales - count
            new_remain_stock = origin_remain_stock + count
            goods.remain_stock = new_remain_stock
            goods.sales = new_sales
            goods.save()
            if launched_goods:
                launched_origin_stock = launched_goods.stock
                launched_origin_sales = launched_goods.sales
                launched_new_stock = launched_origin_stock + count
                launched_new_sales = launched_origin_sales - count
                launched_goods.stock = launched_new_stock
                launched_goods.sales = launched_new_sales
                launched_goods.save()
            else:
                origin_stock = goods.stock
                new_stock = origin_stock + count
                goods.stock = new_stock
                goods.save()
        instance.state = 13
        instance.cancel_time = datetime.datetime.now()
        instance.total_point = 0
        instance.save()
        alidiscounponinfo_set = instance.alidiscountcouponinfo_set.all().filter(status=8).update(status=1, order_id=None)
        try:
            user = instance.customer_user
            pointrecord_set = instance.pointrecord_set.all()
            for pointrecord in pointrecord_set:
                activity_data = pointrecord.activity_data
                activity_data.remain_point += pointrecord.point
                activity_data.state = 3
                activity_data.save()
                user.point += pointrecord.point
                user.save()
                in_pointrecord_data = {}
                in_pointrecord_data['customer_user_id'] = user.id
                in_pointrecord_data['create_time'] = datetime.datetime.now()
                in_pointrecord_data['operate_type'] = 1
                in_pointrecord_data['business_type'] = 4
                in_pointrecord_data['point'] = pointrecord.point
                in_pointrecord_data['point_balance'] = user.point
                in_pointrecord_data['remain_point'] = activity_data.remain_point
                in_pointrecord_data['activity_data_id'] = activity_data.id
                in_pointrecord_data['order_id'] = instance.id
                PointRecord.objects.create(**in_pointrecord_data)
        except:
            pass
        return Response({'success': '取消订单成功'}, status=status.HTTP_200_OK)

    def update_fund_trans(self, request, *args, **kwargs):
        fund_id = self.request.data.get('fund_id', None)
        identity = self.request.data.get('identity', None)
        name = self.request.data.get('name', None)
        if not all([fund_id, identity, name]):
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            orderfundtrans = OrderFundTrans.objects.get(id=fund_id)
        except:
            return Response({'error': '无效转账id'}, status=status.HTTP_400_BAD_REQUEST)
        if orderfundtrans.status == 2:
            return Response({'error': '已转账成功,不可更改转账信息'}, status=status.HTTP_400_BAD_REQUEST)
        if orderfundtrans.status == 3:
            return Response({'error': '转账处理中,不可更改转账信息'}, status=status.HTTP_400_BAD_REQUEST)
        origin_identity = orderfundtrans.identity
        origin_name = orderfundtrans.name
        if origin_identity == identity and origin_name == name:
            return Response({'error': '转账信息无更正'}, status=status.HTTP_400_BAD_REQUEST)
        orderfundtrans.identity = identity
        orderfundtrans.name = name
        orderfundtrans.sub_msg = None
        if orderfundtrans.status == 4:
            origin_out_biz_no = orderfundtrans.out_biz_no
            new_out_biz_no = f'{int(origin_out_biz_no[:1])+1}{origin_out_biz_no[1:]}'
            orderfundtrans.out_biz_no = new_out_biz_no
        orderfundtrans.save()
        order = orderfundtrans.order
        if order.state == 12:
            alipay_obj = C_AlipayOpen()
            trans_amount = str(orderfundtrans.exchange_amount)
            order_title = orderfundtrans.order_title
            out_biz_no = orderfundtrans.out_biz_no
            identity = orderfundtrans.identity
            if orderfundtrans.identity_type == 1:
                result = json.loads(alipay_obj.transunitransfer_user_id(identity, out_biz_no, trans_amount, order_title))
            else:
                name = orderfundtrans.name
                result = json.loads(alipay_obj.transunitransfer_login_id(identity, name, out_biz_no, trans_amount, order_title))
            if result['code'] != '10000':
                orderfundtrans.sub_msg = result['sub_msg']
                orderfundtrans.status = 4
                orderfundtrans.save()
                # try:
                #     ali_fundtrans_template(order.id, str(trans_amount), result['sub_msg'])
                # except:
                #     pass
                return Response({'success': '修改转账信息成功,转账信息仍然有误,转账失败'}, status=status.HTTP_200_OK)
            else:
                orderfundtrans.ali_order_id = result['order_id']
                orderfundtrans.pay_fund_order_id = result['pay_fund_order_id']
                orderfundtrans.trans_date = result['trans_date']
                orderfundtrans.status = 2
                orderfundtrans.save()
                return Response({'success': '修改转账信息成功,并已转账成功'}, status=status.HTTP_200_OK)
        else:
            return Response({'success': '修改转账信息成功'}, status=status.HTTP_200_OK)

    def synchronization_garbage_data(self, request):
        user = self.request.iser
        my_file = self.request.FILES.get('my_file', None)
        start = self.request.data.get('start', None)
        end = self.request.data.get('end', None)
        if not all([start, end]):
            return Response({'error': '请上传交易区间'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            datetime.datetime.strptime(start, '%Y-%m-%d %H:%M:%S')
            datetime.datetime.strptime(end, '%Y-%m-%d %H:%M:%S')
        except:
            return Response({'error': '交易区间参数格式有误'}, status=status.HTTP_400_BAD_REQUEST)
        if not my_file:
            return Response({'error': '请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        type_excel = my_file.name.split('.')[-1]
        file_contents = my_file.read()
        if type_excel not in ['xlsx', 'xls']:
            return Response({'error': '文件格式有误, 请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            wb = xlrd.open_workbook(filename=None, file_contents=file_contents)  # 关键点在于这里
        except:
            return Response({'error': '文件格式有误, 请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        table = wb.sheets()[0]
        title = table.row_values(0)
        if '商户号' not in title:
            return Response({'error': '表格字段缺少"商户号"'}, status=status.HTTP_400_BAD_REQUEST)
        file_name = f'{my_file.name.replace(type_excel, "").replace(".", "")}_{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}.{type_excel}'
        file = pic_upload(file_contents, file_name)
        history = UpdateStockUsableMerchant.objects.create(user_id=user.id, file=file, add_type=3)
        synchronization_garbage_data.delay({'id': history.id, 'start': start, 'end': end})
        # synchronization_garbage_data({'id': history.id, 'start': start, 'end': end})
        # update_stock_usable_merchant({'id': history.id})
        return Response({'success': '上传文件成功'}, status=status.HTTP_200_OK)

    def synchronization_garbage_order(self, request):
        user = self.request.iser
        start = self.request.data.get('start', None)
        end = self.request.data.get('end', None)
        merchant_id = self.request.data.get('merchant_id', None)
        if not all([start, end]):
            return Response({'error': '请上传交易区间'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            start1 = datetime.datetime.strptime(start, '%Y-%m-%d')
            end1 = datetime.datetime.strptime(end, '%Y-%m-%d')
        except:
            return Response({'error': '交易区间参数格式有误'}, status=status.HTTP_400_BAD_REQUEST)
        if start1 > end1:
            return Response({'error': '交易区间参数格式有误'}, status=status.HTTP_400_BAD_REQUEST)
        if merchant_id:
            try:
                merchant = Merchant.objects.get(id=merchant_id)
            except:
                return Response({'error': '错误商户号'}, status=status.HTTP_400_BAD_REQUEST)
            business_no = merchant.wyfMerchantID
        else:
            business_no = None
        synchronization_garbage_order.delay({'business_no': business_no, 'start': start, 'end': end})
        # synchronization_garbage_order({'business_no': business_no, 'start': start, 'end': end})
        return Response({'success': '系统同步交易中'}, status=status.HTTP_200_OK)

    def statistical_order_date(self, request):
        time_min = self.request.query_params.get('time_min', None)
        time_max = self.request.query_params.get('time_max', None)
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        if not all([time_min, time_max]):
            return Response({'error': '缺少时间区间参数!'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            time_min1 = int(time.mktime(time.strptime(str(time_min) + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
            time_max1 = int(time.mktime(time.strptime(str(time_max) + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
        except:
            return Response({'error': '时间区间参数有误!'}, status=status.HTTP_400_BAD_REQUEST)
        if time_max1 - time_min1 > 2678400:
            return Response({'error': '时区不得大于31天!'}, status=status.HTTP_400_BAD_REQUEST)
        time_min1 = time_min + ' 00:00:00'
        time_max1 = time_max + ' 23:59:59'
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = ''
        elif level.level == 1:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        order_amount_data = {}
        order_num_data = {}
        use_amount_data = {}
        use_num_data = {}
        data1 = []
        data2 = []
        data3 = []
        data4 = []
        data = {}
        max_num = 0
        max_amount = 0
        order_amount_sum = 0
        order_num_sum = 0
        use_amount_sum = 0
        use_num_sum = 0
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        if time_min == time_max:
            xAxis = ['0时', '1时', '2时', '3时', '4时', '5时', '6时', '7时', '8时', '9时', '10时', '11时', '12时', '13时', '14时', '15时', '16时', '17时', '18时', '19时', '20时', '21时', '22时', '23时']
            a = 0
            b = 0
            sql = f'''SELECT DATE_FORMAT(create_time,'%H')AS time,COUNT(id)AS id__count,SUM(total_price)AS total_price__sum FROM tb_order_info WHERE create_time>="{time_min1}" AND create_time<="{time_max1}" AND order_species=1 {condition} GROUP BY time;'''
            cursor.execute(sql)
            create_result = cursor.fetchall()
            sql = f'''SELECT DATE_FORMAT(use_time,'%H')AS time,COUNT(id)AS id__count,SUM(total_price)AS total_price__sum FROM tb_order_info WHERE use_time>="{time_min1}" AND use_time<="{time_max1}" AND order_species=1 {condition} GROUP BY time;'''
            cursor.execute(sql)
            use_result = cursor.fetchall()
            if create_result:
                while True:
                    if int(create_result[0]['time']) <= b:
                        break
                    data3.append(0)
                    data4.append(0)
                    b += 1
                for x in create_result:
                    while True:
                        if int(x['time']) < b:
                            break
                        if int(x['time']) == b:
                            data3.append(x['total_price__sum'])
                            data4.append(x['id__count'])
                            order_num_sum += x['id__count']
                            order_amount_sum += x['total_price__sum']
                            b += 1
                            if max_amount < x['total_price__sum']:
                                max_amount = x['total_price__sum']
                            if max_num < x['id__count']:
                                max_num = x['id__count']
                        else:
                            data3.append(0)
                            data4.append(0)
                            b += 1
                while True:
                    if len(data3) >= 24:
                        break
                    data3.append(0)
                    data4.append(0)
            else:
                for i in range(24):
                    data3.append(0)
                    data4.append(0)
            if use_result:
                while True:
                    if int(use_result[0]['time']) <= a:
                        break
                    data1.append(0)
                    data2.append(0)
                    a += 1
                for x in use_result:
                    while True:
                        if int(x['time']) < a:
                            break
                        if int(x['time']) == a:
                            data1.append(x['total_price__sum'])
                            data2.append(x['id__count'])
                            use_num_sum += x['id__count']
                            use_amount_sum += x['total_price__sum']
                            a += 1
                            if max_amount < x['total_price__sum']:
                                max_amount = x['total_price__sum']
                            if max_num < x['id__count']:
                                max_num = x['id__count']
                        else:
                            data1.append(0)
                            data2.append(0)
                            a += 1
                while True:
                    if len(data1) >= 24:
                        break
                    data1.append(0)
                    data2.append(0)
            else:
                for i in range(24):
                    data1.append(0)
                    data2.append(0)
            use_amount_data['name'] = '核销金额'
            use_amount_data['data'] = data1
            use_num_data['name'] = '核销笔数'
            use_num_data['data'] = data2
            order_amount_data['name'] = '下单金额'
            order_amount_data['data'] = data3
            order_num_data['name'] = '下单笔数'
            order_num_data['data'] = data4
        else:
            xAxis = []
            time_min = datetime.datetime.strptime(time_min, "%Y-%m-%d").date()
            time_max = datetime.datetime.strptime(time_max, "%Y-%m-%d").date()
            create_begin_time = time_min
            use_begin_time = time_min
            days_num = (time_max-time_min).days + 1
            begin_day = time_min
            sql = f'''SELECT DATE_FORMAT(create_time,'%Y-%m-%d')AS time,COUNT(id)AS id__count,SUM(total_price)AS total_price__sum FROM tb_order_info WHERE create_time>="{time_min1}" AND create_time<="{time_max1}" AND order_species=1 {condition} GROUP BY time;'''
            cursor.execute(sql)
            create_result = cursor.fetchall()
            sql = f'''SELECT DATE_FORMAT(use_time,'%Y-%m-%d')AS time,COUNT(id)AS id__count,SUM(total_price)AS total_price__sum FROM tb_order_info WHERE use_time>="{time_min1}" AND use_time<="{time_max1}" AND order_species=1 {condition} GROUP BY time;'''
            cursor.execute(sql)
            use_result = cursor.fetchall()
            while True:
                if begin_day > time_max:
                    break
                xAxis.append(datetime.datetime.strftime(begin_day, '%m/%d'))
                begin_day += datetime.timedelta(days=1)
            if create_result:
                while True:
                    if datetime.datetime.strptime(create_result[0]['time'], "%Y-%m-%d").date() <= create_begin_time:
                        break
                    data3.append(0)
                    data4.append(0)
                    create_begin_time += datetime.timedelta(days=1)
                for x in create_result:
                    while True:
                        if datetime.datetime.strptime(x['time'], "%Y-%m-%d").date() < create_begin_time:
                            break
                        if datetime.datetime.strptime(x['time'], "%Y-%m-%d").date() == create_begin_time:
                            data3.append(x['total_price__sum'])
                            data4.append(x['id__count'])
                            order_num_sum += x['id__count']
                            order_amount_sum += x['total_price__sum']
                            if max_amount < x['total_price__sum']:
                                max_amount = x['total_price__sum']
                            if max_num < x['id__count']:
                                max_num = x['id__count']
                        else:
                            data3.append(0)
                            data4.append(0)
                        create_begin_time += datetime.timedelta(days=1)
                while True:
                    if len(data3) >= days_num:
                        break
                    data3.append(0)
                    data4.append(0)
            else:
                for i in range(days_num):
                    data3.append(0)
                    data4.append(0)
            if use_result:
                while True:
                    if datetime.datetime.strptime(use_result[0]['time'], "%Y-%m-%d").date() <= use_begin_time:
                        break
                    data1.append(0)
                    data2.append(0)
                    use_begin_time += datetime.timedelta(days=1)
                for x in use_result:
                    while True:
                        if datetime.datetime.strptime(x['time'], "%Y-%m-%d").date() < use_begin_time:
                            break
                        if datetime.datetime.strptime(x['time'], "%Y-%m-%d").date() == use_begin_time:
                            data1.append(x['total_price__sum'])
                            data2.append(x['id__count'])
                            use_num_sum += x['id__count']
                            use_amount_sum += x['total_price__sum']
                            if max_amount < x['total_price__sum']:
                                max_amount = x['total_price__sum']
                            if max_num < x['id__count']:
                                max_num = x['id__count']
                        else:
                            data1.append(0)
                            data2.append(0)
                        use_begin_time += datetime.timedelta(days=1)
                while True:
                    if len(data1) >= days_num:
                        break
                    data1.append(0)
                    data2.append(0)
            else:
                for i in range(days_num):
                    data1.append(0)
                    data2.append(0)
            use_amount_data['name'] = '核销金额'
            use_amount_data['data'] = data1
            use_num_data['name'] = '核销笔数'
            use_num_data['data'] = data2
            order_amount_data['name'] = '下单金额'
            order_amount_data['data'] = data3
            order_num_data['name'] = '下单笔数'
            order_num_data['data'] = data4
        cursor.close()
        conn.close()
        if max_amount:
            str_max_amount = str(max_amount)
            str_max_num = str(max_num)
            if '.' in str_max_amount:
                len_max_amount = len(str_max_amount[:str_max_amount.find('.')])
            else:
                len_max_amount = len(str_max_amount)
            if '.' in str_max_num:
                len_max_num = len(str_max_num[:str_max_num.find('.')])
            else:
                len_max_num = len(str_max_num)
            to_max_amount = '1'
            to_max_num = '1'
            while True:
                if len(to_max_amount) >= len_max_amount -1:
                    to_max_amount = int(to_max_amount)
                    break
                to_max_amount += '0'
            while True:
                if len(to_max_num) >= len_max_num -1:
                    to_max_num = int(to_max_num)
                    break
                to_max_num += '0'
            max_amount = math.ceil(max_amount/to_max_amount)*to_max_amount
            max_num = math.ceil(max_num/to_max_num)*to_max_num
        data['order_amount_data'] = order_amount_data
        data['order_num_data'] = order_num_data
        data['use_amount_data'] = use_amount_data
        data['use_num_data'] = use_num_data
        data['max_amount'] = max_amount
        data['max_num'] = max_num
        data['xAxis'] = xAxis
        data['use_amount_sum'] = use_amount_sum
        data['use_num_sum'] = use_num_sum
        data['order_amount_sum'] = order_amount_sum
        data['order_num_sum'] = order_num_sum
        return Response(data, status=status.HTTP_200_OK)

    def export_statistical_order_date(self, request):
        time_min = self.request.query_params.get('time_min', None)
        time_max = self.request.query_params.get('time_max', None)
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        if not all([time_min, time_max]):
            return Response({'error': '缺少时间区间参数!'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            time_min1 = int(time.mktime(time.strptime(str(time_min) + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
            time_max1 = int(time.mktime(time.strptime(str(time_max) + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
        except:
            return Response({'error': '时间区间参数有误!'}, status=status.HTTP_400_BAD_REQUEST)
        time_min1 = time_min + ' 00:00:00'
        time_max1 = time_max + ' 23:59:59'
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = ''
        elif level.level == 1:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        if time_min == time_max:
            sql = f'''
            (SELECT a.*,b.use__count,use__sum,b.time AS c_time FROM (SELECT DATE_FORMAT(create_time,'%H时')AS time,COUNT(id)AS order__count,SUM(total_price)AS order__sum FROM tb_order_info WHERE create_time>="{time_min1}" AND create_time<="{time_max1}" AND order_species=1 {condition} GROUP BY time)AS a LEFT JOIN (SELECT DATE_FORMAT(use_time,'%H时')AS time,COUNT(id)AS use__count,SUM(total_price)AS use__sum FROM tb_order_info WHERE use_time>="{time_min1}" AND use_time<="{time_max1}" AND order_species=1 {condition} GROUP BY time)AS b ON a.time=b.time) 
            UNION
            (SELECT a.*,b.use__count,use__sum,b.time AS c_time FROM (SELECT DATE_FORMAT(create_time,'%H时')AS time,COUNT(id)AS order__count,SUM(total_price)AS order__sum FROM tb_order_info WHERE create_time>="{time_min1}" AND create_time<="{time_max1}" AND order_species=1 {condition} GROUP BY time)AS a RIGHT JOIN (SELECT DATE_FORMAT(use_time,'%H时')AS time,COUNT(id)AS use__count,SUM(total_price)AS use__sum FROM tb_order_info WHERE use_time>="{time_min1}" AND use_time<="{time_max1}" AND order_species=1 {condition} GROUP BY time)AS b ON a.time=b.time);
            '''
        else:
            sql = f'''
            (SELECT a.*,b.use__count,use__sum,b.time AS c_time FROM (SELECT DATE_FORMAT(create_time,'%Y-%m-%d')AS time,COUNT(id)AS order__count,SUM(total_price)AS order__sum FROM tb_order_info WHERE create_time>="{time_min1}" AND create_time<="{time_max1}" AND order_species=1 {condition} GROUP BY time)AS a LEFT JOIN (SELECT DATE_FORMAT(use_time,'%Y-%m-%d')AS time,COUNT(id)AS use__count,SUM(total_price)AS use__sum FROM tb_order_info WHERE use_time>="{time_min1}" AND use_time<="{time_max1}" AND order_species=1 {condition} GROUP BY time)AS b ON a.time=b.time)
            UNION
            (SELECT a.*,b.use__count,use__sum,b.time AS c_time FROM (SELECT DATE_FORMAT(create_time,'%Y-%m-%d')AS time,COUNT(id)AS order__count,SUM(total_price)AS order__sum FROM tb_order_info WHERE create_time>="{time_min1}" AND create_time<="{time_max1}" AND order_species=1 {condition} GROUP BY time)AS a RIGHT JOIN (SELECT DATE_FORMAT(use_time,'%Y-%m-%d')AS time,COUNT(id)AS use__count,SUM(total_price)AS use__sum FROM tb_order_info WHERE use_time>="{time_min1}" AND use_time<="{time_max1}" AND order_species=1 {condition} GROUP BY time)AS b ON a.time=b.time);
            '''
        cursor.execute(sql)
        ziduan = ['日期', '机构名称', '邮米下单金额', '邮米下单笔数', '邮米核销金额', '邮米核销笔数']
        cols = ['time', 'name', 'order__sum', 'order__count', 'use__sum', 'use__count']
        response = StreamingHttpResponse(self.get_statistical_order_date_fromat(cols, cursor, conn, ziduan, level.name))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('邮米结算统计.csv'))
        return response

    def get_statistical_order_date_fromat(self, cols, cursor, conn, ziduan, level_name):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'name':
                    tmp_str += '"%s",' % (level_name)
                elif col == 'time':
                    tmp_str += '"%s",' % (str(row[col]) if row[col] else row['c_time'])
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else 0)
            yield tmp_str.strip(',') + "\n"

    def statistical_order_level(self, request):
        level_id = self.request.query_params.get('level', None)
        time_min = self.request.query_params.get('time_min', None)
        time_max = self.request.query_params.get('time_max', None)
        dimensionality = self.request.query_params.get('dimensionality', '1')
        user = self.request.iser
        level = user.level
        level_level_id = level.level
        level_list = get_level_list(level)
        if level_id:
            if int(level_id) not in level_list:
                return Response({'error': '您无权限查看当前机构'}, status=status.HTTP_400_BAD_REQUEST)
            level = Level.objects.get(id=level_id)
            level_list = get_level_list(level)
            level_level_id = level.level
        condition = 'WHERE order_species=1'
        if time_min or time_max:
            try:
                time_min1 = int(time.mktime(time.strptime(str(time_min) + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
                time_max1 = int(time.mktime(time.strptime(str(time_max) + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
            except:
                return Response({'error': '时间区间参数有误!'}, status=status.HTTP_400_BAD_REQUEST)
            time_min = time_min + ' 00:00:00'
            time_max = time_max + ' 23:59:59'
            # create_queryset = queryset.filter(create_time__gte=time_min, create_time__lte=time_max)
            # use_queryset = queryset.filter(use_time__gte=time_min, use_time__lte=time_max)
            create_condition = condition + f' AND create_time>="{time_min}" AND create_time<="{time_max}"'
            use_condition = condition + f' AND use_time>="{time_min}" AND use_time<="{time_max}"'
        else:
            # create_queryset = queryset
            # use_queryset = queryset
            create_condition = condition
            use_condition = condition
        if level_level_id not in [0, 1, 2] and dimensionality == '1':
            return Response({'error': '当前机构等级不能查看市级数据统计'}, status=status.HTTP_400_BAD_REQUEST)
        if level_level_id not in [0, 1, 2, 3] and dimensionality == '2':
            return Response({'error': '当前机构等级不能查看区县级数据统计'}, status=status.HTTP_400_BAD_REQUEST)
        level_query = Level.objects.filter(id__in=level_list)
        data = []
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        if str(dimensionality) == '1':
            level_query = level_query.filter(level=2)
            # page = self.paginate_queryset(level_query)
            # sql_level_list = []
            # for level in page:
            #     sql_level_list.append(level.id)
            sql_level_list = list(level_query.values_list('id', flat=True))
            if len(sql_level_list) == 1:
                sql_level_list = str(tuple(sql_level_list)).replace(',', '')
            else:
                sql_level_list = str(tuple(sql_level_list))
            create_condition = create_condition + f'AND level_id IN (SELECT id FROM tb_u_level WHERE city_id IN {sql_level_list})'
            use_condition = use_condition + f'AND level_id IN (SELECT id FROM tb_u_level WHERE city_id IN {sql_level_list})'
            sql = f'''
            SELECT * FROM (SELECT e.*,f.order_num_sum,order_amount_sum FROM (SELECT id,prov_name,city_name,district_name,branch_name FROM tb_u_level WHERE id IN {sql_level_list})AS e LEFT JOIN (SELECT COUNT(c.id)AS order_num_sum,SUM(c.total_price)AS order_amount_sum,MAX(c.city_id)AS city_id FROM (SELECT a.id,total_price,b.city_id FROM (SELECT id,total_price,level_id FROM tb_order_info {create_condition})AS a LEFT JOIN (SELECT id,city_id FROM tb_u_level)AS b ON a.level_id=b.id)AS c GROUP BY c.city_id)AS f ON e.id=f.city_id)AS g LEFT JOIN (SELECT COUNT(c.id)AS use_num_sum,SUM(c.total_price)AS use_amount_sum,MAX(c.city_id)AS city_id FROM (SELECT a.id,total_price,b.city_id FROM (SELECT id,total_price,level_id FROM tb_order_info {use_condition})AS a LEFT JOIN (SELECT id,city_id FROM tb_u_level)AS b ON a.level_id=b.id)AS c GROUP BY c.city_id)AS h ON g.id=h.city_id;
            '''
            cursor.execute(sql)
            rows = cursor.fetchall()
            cursor.close()
            conn.close()
            for row in rows:
                dict1 = {}
                dict1['id'] = row['id']
                dict1['province'] = row['prov_name']
                dict1['city'] = row['city_name']
                dict1['district'] = ''
                dict1['branch'] = ''
                dict1['use_amount_sum'] = row['use_amount_sum'] if row['use_amount_sum'] else 0
                dict1['use_num_sum'] = row['use_num_sum'] if row['use_num_sum'] else 0
                dict1['order_amount_sum'] = row['order_amount_sum'] if row['order_amount_sum'] else 0
                dict1['order_num_sum'] = row['order_num_sum'] if row['order_num_sum'] else 0
                data.append(dict1)
            return Response(data, status=status.HTTP_200_OK)
            return self.get_paginated_response(data)
        elif str(dimensionality) == '2':
            level_query = level_query.filter(level=3)
            # page = self.paginate_queryset(level_query)
            # sql_level_list = []
            # for level in page:
            #     sql_level_list.append(level.id)
            sql_level_list = list(level_query.values_list('id', flat=True))
            if len(sql_level_list) == 1:
                sql_level_list = str(tuple(sql_level_list)).replace(',', '')
            else:
                sql_level_list = str(tuple(sql_level_list))
            create_condition = create_condition + f'AND level_id IN (SELECT id FROM tb_u_level WHERE district_id IN {sql_level_list})'
            use_condition = use_condition + f'AND level_id IN (SELECT id FROM tb_u_level WHERE district_id IN {sql_level_list})'
            sql = f'''
            SELECT * FROM (SELECT e.*,f.order_num_sum,order_amount_sum FROM (SELECT id,prov_name,city_name,district_name,branch_name FROM tb_u_level WHERE id IN {sql_level_list})AS e LEFT JOIN (SELECT COUNT(c.id)AS order_num_sum,SUM(c.total_price)AS order_amount_sum,MAX(c.district_id)AS district_id FROM (SELECT a.id,total_price,b.district_id FROM (SELECT id,total_price,level_id FROM tb_order_info {create_condition})AS a LEFT JOIN (SELECT id,district_id FROM tb_u_level)AS b ON a.level_id=b.id)AS c GROUP BY c.district_id)AS f ON e.id=f.district_id)AS g LEFT JOIN (SELECT COUNT(c.id)AS use_num_sum,SUM(c.total_price)AS use_amount_sum,MAX(c.district_id)AS district_id FROM (SELECT a.id,total_price,b.district_id FROM (SELECT id,total_price,level_id FROM tb_order_info {use_condition})AS a LEFT JOIN (SELECT id,district_id FROM tb_u_level)AS b ON a.level_id=b.id)AS c GROUP BY c.district_id)AS h ON g.id=h.district_id;
            '''
            cursor.execute(sql)
            rows = cursor.fetchall()
            cursor.close()
            conn.close()
            for row in rows:
                dict1 = {}
                dict1['id'] = row['id']
                dict1['province'] = row['prov_name']
                dict1['city'] = row['city_name']
                dict1['district'] = row['district_name']
                dict1['branch'] = ''
                dict1['use_amount_sum'] = row['use_amount_sum'] if row['use_amount_sum'] else 0
                dict1['use_num_sum'] = row['use_num_sum'] if row['use_num_sum'] else 0
                dict1['order_amount_sum'] = row['order_amount_sum'] if row['order_amount_sum'] else 0
                dict1['order_num_sum'] = row['order_num_sum'] if row['order_num_sum'] else 0
                data.append(dict1)
            return Response(data, status=status.HTTP_200_OK)
            # return self.get_paginated_response(data)
        else:
            level_query = level_query.filter(level=4)
            # page = self.paginate_queryset(level_query)
            # for level in page:
            #     dict1 = {}
            #     dict1['id'] = level.id
            #     dict1['province'] = level.parent.parent.parent.name
            #     dict1['city'] = level.parent.parent.name
            #     dict1['district'] = level.parent.name
            #     dict1['branch'] = level.name
            #     use_queryset1 = use_queryset.filter(level_id=level.id)
            #     create_queryset1 = create_queryset.filter(level_id=level.id)
            #     try:
            #         total_price__sum = use_queryset1.aggregate(Sum('total_price'))['total_price__sum']
            #         dict1['use_amount_sum'] = total_price__sum if total_price__sum else 0
            #         dict1['use_num_sum'] = use_queryset1.count()
            #     except:
            #         dict1['use_amount_sum'] = 0
            #         dict1['use_num_sum'] = 0
            #     try:
            #         total_price__sum = create_queryset1.aggregate(Sum('total_price'))['total_price__sum']
            #         dict1['order_amount_sum'] = total_price__sum if total_price__sum else 0
            #         dict1['order_num_sum'] = create_queryset1.count()
            #     except:
            #         dict1['order_amount_sum'] = 0
            #         dict1['order_num_sum'] = 0
            #     data.append(dict1)
            # return self.get_paginated_response(data)
            sql_level_list = list(level_query.values_list('id', flat=True))
            if len(sql_level_list) == 1:
                sql_level_list = str(tuple(sql_level_list)).replace(',', '')
            else:
                sql_level_list = str(tuple(sql_level_list))
            create_condition = create_condition + f'AND level_id IN (SELECT id FROM tb_u_level WHERE branch_id IN {sql_level_list})'
            use_condition = use_condition + f'AND level_id IN (SELECT id FROM tb_u_level WHERE branch_id IN {sql_level_list})'
            sql = f'''
            SELECT * FROM (SELECT e.*,f.order_num_sum,order_amount_sum FROM (SELECT id,prov_name,city_name,district_name,branch_name FROM tb_u_level WHERE id IN {sql_level_list})AS e LEFT JOIN (SELECT COUNT(c.id)AS order_num_sum,SUM(c.total_price)AS order_amount_sum,MAX(c.branch_id)AS branch_id FROM (SELECT a.id,total_price,b.branch_id FROM (SELECT id,total_price,level_id FROM tb_order_info {create_condition})AS a LEFT JOIN (SELECT id,branch_id FROM tb_u_level)AS b ON a.level_id=b.id)AS c GROUP BY c.branch_id)AS f ON e.id=f.branch_id)AS g LEFT JOIN (SELECT COUNT(c.id)AS use_num_sum,SUM(c.total_price)AS use_amount_sum,MAX(c.branch_id)AS branch_id FROM (SELECT a.id,total_price,b.branch_id FROM (SELECT id,total_price,level_id FROM tb_order_info {use_condition})AS a LEFT JOIN (SELECT id,branch_id FROM tb_u_level)AS b ON a.level_id=b.id)AS c GROUP BY c.branch_id)AS h ON g.id=h.branch_id;
            '''
            cursor.execute(sql)
            rows = cursor.fetchall()
            cursor.close()
            conn.close()
            for row in rows:
                dict1 = {}
                dict1['id'] = row['id']
                dict1['province'] = row['prov_name']
                dict1['city'] = row['city_name']
                dict1['district'] = row['district_name']
                dict1['branch'] = row['branch_name']
                dict1['use_amount_sum'] = row['use_amount_sum'] if row['use_amount_sum'] else 0
                dict1['use_num_sum'] = row['use_num_sum'] if row['use_num_sum'] else 0
                dict1['order_amount_sum'] = row['order_amount_sum'] if row['order_amount_sum'] else 0
                dict1['order_num_sum'] = row['order_num_sum'] if row['order_num_sum'] else 0
                data.append(dict1)
            return Response(data, status=status.HTTP_200_OK)

    def export_statistical_order_level(self, request):
        time_min = self.request.query_params.get('time_min', None)
        time_max = self.request.query_params.get('time_max', None)
        dimensionality = self.request.query_params.get('dimensionality', '1')
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        if not all([time_min, time_max]):
            return Response({'error': '缺少时间区间参数!'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            time_min1 = int(time.mktime(time.strptime(str(time_min) + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
            time_max1 = int(time.mktime(time.strptime(str(time_max) + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
        except:
            return Response({'error': '时间区间参数有误!'}, status=status.HTTP_400_BAD_REQUEST)
        time_min1 = time_min + ' 00:00:00'
        time_max1 = time_max + ' 23:59:59'
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        period = f'{time_min}~{time_max}'
        if level.level == 0:
            condition = ''
        elif level.level == 1:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        if dimensionality == '1':
            keywork = 'city_name'
        elif dimensionality == '2':
            keywork = 'district_name'
        else:
            keywork = 'branch_name'
        sql = f'''
        (SELECT e.*,f.use_count,use_sum,f.prov_name AS use_prov_name,f.city_name AS use_city_name,f.district_name AS use_district_name,f.branch_name AS use_branch_name FROM (SELECT COUNT(c.id)AS order_count,SUM(c.total_price)AS order_sum,MAX(c.prov_name)AS prov_name,MAX(c.city_name)AS city_name,MAX(c.district_name)AS district_name,MAX(c.branch_name)AS branch_name FROM (SELECT a.id,total_price,b.prov_name,city_name,district_name,branch_name FROM (SELECT id,total_price,level_id FROM tb_order_info WHERE order_species=1 AND create_time>="{time_min1}" AND create_time<="{time_max1}" {condition})AS a LEFT JOIN (SELECT id,prov_name,city_name,district_name,branch_name FROM tb_u_level)AS b ON a.level_id=b.id)AS c GROUP BY c.{keywork})AS e LEFT JOIN (SELECT COUNT(c.id)AS use_count,SUM(c.total_price)AS use_sum,MAX(c.prov_name)AS prov_name,MAX(c.city_name)AS city_name,MAX(c.district_name)AS district_name,MAX(c.branch_name)AS branch_name FROM (SELECT a.id,total_price,b.prov_name,city_name,district_name,branch_name FROM (SELECT id,total_price,level_id FROM tb_order_info WHERE order_species=1 AND use_time>="{time_min1}" AND use_time<="{time_max1}" {condition})AS a LEFT JOIN (SELECT id,prov_name,city_name,district_name,branch_name FROM tb_u_level)AS b ON a.level_id=b.id)AS c GROUP BY c.{keywork})AS f ON e.{keywork}=f.{keywork})
        UNION
        (SELECT e.*,f.use_count,use_sum,f.prov_name AS use_prov_name,f.city_name AS use_city_name,f.district_name AS use_district_name,f.branch_name AS use_branch_name FROM (SELECT COUNT(c.id)AS order_count,SUM(c.total_price)AS order_sum,MAX(c.prov_name)AS prov_name,MAX(c.city_name)AS city_name,MAX(c.district_name)AS district_name,MAX(c.branch_name)AS branch_name FROM (SELECT a.id,total_price,b.prov_name,city_name,district_name,branch_name FROM (SELECT id,total_price,level_id FROM tb_order_info WHERE order_species=1 AND create_time>="{time_min1}" AND create_time<="{time_max1}" {condition})AS a LEFT JOIN (SELECT id,prov_name,city_name,district_name,branch_name FROM tb_u_level)AS b ON a.level_id=b.id)AS c GROUP BY c.{keywork})AS e RIGHT JOIN (SELECT COUNT(c.id)AS use_count,SUM(c.total_price)AS use_sum,MAX(c.prov_name)AS prov_name,MAX(c.city_name)AS city_name,MAX(c.district_name)AS district_name,MAX(c.branch_name)AS branch_name FROM (SELECT a.id,total_price,b.prov_name,city_name,district_name,branch_name FROM (SELECT id,total_price,level_id FROM tb_order_info WHERE order_species=1 AND use_time>="{time_min1}" AND use_time<="{time_max1}" {condition})AS a LEFT JOIN (SELECT id,prov_name,city_name,district_name,branch_name FROM tb_u_level)AS b ON a.level_id=b.id)AS c GROUP BY c.{keywork})AS f ON e.{keywork}=f.{keywork})
        '''
        cursor.execute(sql)
        ziduan = ['周期', '市公司', '区县公司', '网点', '下单金额', '下单笔数', '核销金额', '核销笔数']
        cols = ['period', 'city_name', 'district_name', 'branch_name', 'order_sum', 'order_count', 'use_sum', 'use_count']
        response = StreamingHttpResponse(self.get_statistical_order_level_fromat(cols, cursor, conn, ziduan, period, dimensionality))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('机构数据统计.csv'))
        return response

    def get_statistical_order_level_fromat(self, cols, cursor, conn, ziduan, period, dimensionality):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'period':
                    tmp_str += '"%s",' % (period)
                elif col == 'city_name':
                    tmp_str += '"%s",' % (row[col] if row[col] else row['use_city_name'])
                elif col == 'district_name':
                    tmp_str += '"%s",' % ('' if dimensionality == '1' else (row[col] if row[col] else row['use_district_name']))
                elif col == 'branch_name':
                    tmp_str += '"%s",' % ((row[col] if row[col] else row['use_branch_name']) if dimensionality == '3' else '')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else 0)
            yield tmp_str.strip(',') + "\n"

    def add_express(self, request):
        user = self.request.iser
        express_array = self.request.data.get('express_array', None)
        order_id = self.request.data.get('order_id', None)
        if not all([express_array, order_id]):
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            instance = self.get_queryset().get(id=order_id)
        except:
            return Response({'error': '无效订单id'}, status=status.HTTP_400_BAD_REQUEST)
        if instance.state not in [6, 9]:
            return Response({'error': '订单状态有误'}, status=status.HTTP_400_BAD_REQUEST)
        with transaction.atomic():
            save_id = transaction.savepoint()
            try:
                for express in express_array:
                    try:
                        ExpressType.objects.get(id=express['express_type_id'])
                    except:
                        transaction.savepoint_rollback(save_id)
                        return Response({'error': '无效快递类型'}, status=status.HTTP_400_BAD_REQUEST)
                    express_data = {}
                    express_data['express_type_id'] = express['express_type_id']
                    express_data['express_number'] = express['express_number']
                    express, create = Express.objects.get_or_create(defaults=express_data, express_number=express['express_number'])
                    relevance_data = {}
                    relevance_data['order_id'] = instance.id
                    relevance_data['express_id'] = express.id
                    ExpressOrderRelevance.objects.get_or_create(defaults=relevance_data, order_id=relevance_data['order_id'], express_id=relevance_data['express_id'])
                instance.state = 6
                instance.save()
                OrderAdvance.objects.create(
                    order_id=instance.id,
                    status=3,
                    remark='添加快递单号',
                    user_id=user.id
                )
                try:
                    express_send.delay({'id': instance.id, 'express_id': express.id})
                except:
                    pass
            except Exception as e:
                logger.error(e)
                transaction.savepoint_rollback(save_id)
                return Response({'error': '操作失败'}, status=status.HTTP_400_BAD_REQUEST)
            transaction.savepoint_commit(save_id)
            return Response({'success': '操作成功'}, status=status.HTTP_200_OK)

    def delete_express(self,request):
        user = self.request.iser
        express_id = self.request.data.get('express_id', None)
        order_id = self.request.data.get('order_id', None)
        if not all([order_id, express_id]):
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            instance = self.get_queryset().get(id=order_id)
        except:
            return Response({'error': '无效订单id'}, status=status.HTTP_400_BAD_REQUEST)
        delete_instance = ExpressOrderRelevance.objects.filter(order_id=order_id, express_id=express_id)
        if not delete_instance:
            return Response({'error': '解绑失败,请联系管理员'}, status=status.HTTP_400_BAD_REQUEST)
        delete_instance.delete()
        check_relevance = ExpressOrderRelevance.objects.filter(order_id=order_id)
        if not check_relevance:
            instance.state = 9
            instance.save()
        OrderAdvance.objects.create(
            order_id=instance.id,
            status=3,
            remark='解绑快递单号',
            user_id=user.id
        )
        return Response({'success': '操作成功'}, status=status.HTTP_200_OK)

    def refund_order(self, request):
        user = self.request.iser
        order_id = self.request.data.get('order_id', None)
        refund_money = self.request.data.get('refund_money', None)
        remark = self.request.data.get('remark', None)
        refund_type = self.request.data.get('refund_type', None)
        if not order_id:
            return Response({'error': '请上传订单id'}, status=status.HTTP_400_BAD_REQUEST)
        order = self.get_queryset().filter(id=order_id).first()
        if order.state != 9:
            return Response({'error': '仅发货中的订单可以退款'}, status=status.HTTP_400_BAD_REQUEST)
        if user.id not in [1, 12105, 9297, 65139]:
            return Response({'error': '您无权限操作'}, status=status.HTTP_400_BAD_REQUEST)
        if order.order_species != 2:
            return Response({'error': '仅支持微邮惠商圈订单退款'}, status=status.HTTP_400_BAD_REQUEST)
        url = 'https://api.gdwxyf.com/api/refund.do'
        datetime_now = datetime.datetime.now()
        order_id = f'{datetime_now.strftime("%Y%m%d%H%M%S")}{"%06d" % randint(0, 999999)}'
        if str(refund_type) == '1':
            body = {
                "program_id": "202316837141378884",
                "shop_id": order.merchant.ruiyinxinMerchantID,
                "refund_order_id": order_id,
                "order_id": order.orderNum
            }
        else:
            return Response({'error': '暂不支持部分退款'}, status=status.HTTP_400_BAD_REQUEST)
            body = {
                "program_id": "202316837141378884",
                "shop_id": order.merchant.ruiyinxinMerchantID,
                "refund_order_id": order_id,
                "order_id": order.orderNum
            }
        key = '6715C7D4435343BA0459EAEC2334D81B'
        sign = get_sign(body, key)
        body['sign'] = sign
        response = requests.post(url=url, json=body)
        result = response.json()
        if result['result_code'] == 'success':
            refund_order_data = {}
            refund_order_data['order_id'] = order.id
            refund_order_data['user_id'] = user.id
            refund_order_data['operator_role'] = user.role_id
            refund_order_data['refund_order_id'] = order_id
            refund_order_data['refund_transaction_id'] = result['refund_transaction_id']
            refund_order_data['plat_refund_order_id'] = result['plat_refund_order_id']
            refund_order_data['refund_money'] = result['refund_money']
            refund_order_data['refund_procedure_money'] = result['commission']
            refund_order_data['refund_time'] = datetime.datetime.strptime(result['refund_end_time'], '%Y%m%d%H%M%S')
            refund_order_data['remark'] = remark
            RefundOrderInfo.objects.create(**refund_order_data)
            order.state = 8
            # order.refund_money += Decimal(result['refund_money'])
            order.refund_money = order.realpay_amount
            order.out_refund_no = order_id
            order.refund_id = result['refund_transaction_id']
            order.save()
            try:
                after_sale = AfterSaleApplicationForm.objects.filter(order_id=order.id, form_status=1).last()
                if after_sale:
                    after_sale.form_status = 2
                    after_sale.refund_time = datetime.datetime.strptime(result['refund_end_time'], '%Y%m%d%H%M%S')
                    after_sale.save()
            except:
                pass
            ordergoods_set = order.ordergoods_set.all()
            for ordergoods in ordergoods_set:
                goods = ordergoods.goods
                launched_goods = ordergoods.launched_goods
                count = ordergoods.count
                origin_sales = goods.sales
                origin_remain_stock = goods.remain_stock
                new_sales = origin_sales - count
                new_remain_stock = origin_remain_stock + count
                goods.sales = new_sales
                goods.remain_stock = new_remain_stock
                goods.save()
                if launched_goods:
                    launched_origin_stock = launched_goods.stock
                    launched_origin_sales = launched_goods.sales
                    launched_new_stock = launched_origin_stock + count
                    launched_new_sales = launched_origin_sales - count
                    launched_goods.stock = launched_new_stock
                    launched_goods.sales = launched_new_sales
                    launched_goods.save()
                else:
                    origin_stock = goods.stock
                    new_stock = origin_stock + count
                    goods.stock = new_stock
                    goods.save()

            return Response({'success': '退款成功'}, status=status.HTTP_200_OK)
        else:
            refund_order_data = {}
            refund_order_data['refund_status'] = 2
            refund_order_data['operator_role'] = user.role_id
            refund_order_data['order_id'] = order.id
            refund_order_data['user_id'] = user.id
            refund_order_data['return_msg'] = result['error_msg']
            refund_order_data['refund_order_id'] = order_id
            refund_order_data['refund_time'] = datetime_now
            refund_order_data['remark'] = remark
            RefundOrderInfo.objects.create(**refund_order_data)
            return Response({'error': f'退款失败,原因:{result["error_msg"]}'}, status=status.HTTP_400_BAD_REQUEST)

    def batch_add_express(self, request):
        user = self.request.iser
        check_history = ExpressQuest.objects.filter(is_enter=1)
        if check_history:
            is_enter = 4
        else:
            is_enter = 1
        my_file = self.request.FILES.get('my_file', None)
        if not my_file:
            return Response({'error': '请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        type_excel = my_file.name.split('.')[-1]
        file_contents = my_file.read()
        if type_excel not in ['xlsx', 'xls']:
            return Response({'error': '文件格式有误, 请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            wb = xlrd.open_workbook(filename=None, file_contents=file_contents)  # 关键点在于这里
        except:
            return Response({'error': '文件格式有误, 请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        table = wb.sheets()[0]
        nrows = table.nrows - 1
        title = table.row_values(0)
        if '订单号' not in title:
            return Response({'error': '表格字段缺少"订单号"'}, status=status.HTTP_400_BAD_REQUEST)
        if '快递单号' not in title:
            return Response({'error': '表格字段缺少"快递单号"'}, status=status.HTTP_400_BAD_REQUEST)
        if '快递类型' not in title:
            return Response({'error': '表格字段缺少"快递类型"'}, status=status.HTTP_400_BAD_REQUEST)
        file_name = f'{my_file.name.replace(type_excel, "").replace(".", "")}_{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}.{type_excel}'
        file = pic_upload(file_contents, file_name)
        history = ExpressQuest.objects.create(user_id=user.id, file=file, is_enter=is_enter, line_number=nrows)
        if is_enter == 1:
            # execute_express_quest({'id': history.id})
            execute_express_quest.delay({'id': history.id})
        return Response({'success': '上传文件成功'}, status=status.HTTP_200_OK)

    def receive_goods(self, request):
        user = self.request.iser
        order_id = self.request.data.get('order_id', None)
        if not order_id:
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            order = self.get_queryset().get(id=order_id)
        except:
            return Response({'error': '无效订单id'}, status=status.HTTP_400_BAD_REQUEST)
        if order.state != 6:
            return Response({'error': '订单状态有误'}, status=status.HTTP_400_BAD_REQUEST)
        order.state = 14
        order.save()
        # OrderAdvance.objects.create(
        #     order_id=order.id,
        #     status=4,
        #     remark='超管确认收货',
        #     user_id=user.id
        # )
        try:
            # if order.is_profit_sharing in [1, 3]:
            #     if order.is_profit_sharing == 1:
            #         out_trade_no = f'{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}{"%06d" % randint(0, 999999)}'
            #         body = {
            #             "program_id": "202316837141378884",
            #             "shop_id": order.merchant.ruiyinxinMerchantID,
            #             "order_id": out_trade_no,
            #             "out_trade_no": order.orderNum,
            #             "is_profit": "0"
            #         }
            #         result = ryx_profitsharing_order(body)
            #     else:
            #         out_trade_no = f'{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}{"%06d" % randint(0, 999999)}'
            #         body = {
            #             "program_id": "202316837141378884",
            #             "shop_id": order.merchant.ruiyinxinMerchantID,
            #             "order_id": out_trade_no,
            #             "out_trade_no": order.orderNum,
            #             "is_profit": "2"
            #         }
            #         result = ryx_profitsharing_order(body)
            #     profitorder = OrderProfitSharing.objects.filter(order_id=order.id).first()
            #     if not profitorder:
            #         profitorder, create = OrderProfitSharing.objects.get_or_create(defaults={'order_id': order.id, "out_trade_no": out_trade_no}, order_id=order.id)
            #     if result['result_code'] == 'success':
            #         order.is_profit_sharing = 2
            #         profitorder.profit_order_id = result['profit_order_id']
            #         profitorder.profit_time = datetime.datetime.now()
            #         profitorder.save()
            #         receipts_time = datetime.datetime.now().strftime('%Y-%m-%d')
            #         bill = MerchantBill.objects.filter(receipts_time=receipts_time, merchant_id=order.merchant_id, bill_type=order.order_species).first()
            #         if not bill:
            #             bill_data = {}
            #             bill_data['merchant_id'] = order.merchant_id
            #             bill_data['receipts_time'] = receipts_time
            #             bill_data['amount'] = order.total_price - order.freight
            #             bill_data['commission'] = order.freight
            #             bill_data['level_id'] = order.merchant.level_id
            #             bill_data['payable_amount'] = order.total_price - order.freight
            #             bill_data['channel_id'] = 3
            #             bill_data['bill_type'] = order.order_species
            #             bill, create = MerchantBill.objects.get_or_create(defaults=bill_data, receipts_time=receipts_time, merchant_id=order.merchant_id, bill_type=order.order_species)
            #         else:
            #             bill.amount = bill.amount + order.total_price
            #             bill.commission = bill.commission + order.freight
            #             bill.payable_amount = bill.payable_amount + order.total_price - order.freight
            #             bill.save()
            #         order.payable_settled_amount = order.total_price - order.freight
            #         order.bill_id = bill.id
            #         order.save()
            #     else:
            #         profitorder.error_msg = result['error_msg']
            #         profitorder.profit_status = 2
            #         profitorder.save()
            if order.is_profit_sharing == 1:
                out_trade_no = f'{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}{"%06d" % randint(0, 999999)}'
                body = {
                    "program_id": "202316837141378884",
                    "shop_id": order.merchant.ruiyinxinMerchantID,
                    "order_id": out_trade_no,
                    "out_trade_no": order.orderNum,
                    "is_profit": "0"
                }
                result = ryx_profitsharing_order(body)
                profitorder = OrderProfitSharing.objects.filter(order_id=order.id).first()
                if not profitorder:
                    profitorder, create = OrderProfitSharing.objects.get_or_create(defaults={'order_id': order.id, "out_trade_no": out_trade_no}, order_id=order.id)
                if result['result_code'] == 'success':
                    order.is_profit_sharing = 2
                    order.save()
                    profitorder.profit_order_id = result['profit_order_id']
                    profitorder.profit_time = datetime.datetime.now()
                    profitorder.save()
                    OrderAdvance.objects.create(
                        order_id=order.id,
                        status=5,
                        remark='超管确认收货,解冻资金',
                        user_id=user.id
                    )
                    receipts_time = datetime.datetime.now().strftime('%Y-%m-%d')
                    bill = MerchantBill.objects.filter(receipts_time=receipts_time, merchant_id=order.merchant_id,
                                                       bill_type=order.order_species).first()
                    if not bill:
                        bill_data = {}
                        bill_data['merchant_id'] = order.merchant_id
                        bill_data['receipts_time'] = receipts_time
                        bill_data['amount'] = order.total_price
                        bill_data['commission'] = order.freight
                        bill_data['level_id'] = order.merchant.level_id
                        bill_data['payable_amount'] = order.total_price - order.freight
                        bill_data['channel_id'] = 3
                        bill_data['bill_type'] = order.order_species
                        bill, create = MerchantBill.objects.get_or_create(defaults=bill_data,
                                                                          receipts_time=receipts_time,
                                                                          merchant_id=order.merchant_id,
                                                                          bill_type=order.order_species)
                    else:
                        bill.amount = bill.amount + order.total_price
                        bill.commission = bill.commission + order.freight
                        bill.payable_amount = bill.payable_amount + order.total_price - order.freight
                        bill.save()
                    order.payable_settled_amount = order.total_price - order.freight
                    order.bill_id = bill.id
                    order.save()
                else:
                    order.is_profit_sharing = 2
                    order.save()
                    profitorder.error_msg = result['error_msg']
                    profitorder.profit_status = 2
                    profitorder.save()
                    OrderAdvance.objects.create(
                        order_id=order.id,
                        status=4,
                        remark='超管确认收货,解冻资金出错',
                        user_id=user.id
                    )
            elif order.is_profit_sharing == 3:
                out_trade_no = f'{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}{"%06d" % randint(0, 999999)}'
                body = {
                    "program_id": "202316837141378884",
                    "shop_id": order.merchant.ruiyinxinMerchantID,
                    "order_id": out_trade_no,
                    "out_trade_no": order.orderNum,
                    "is_profit": "2"
                }
                result = ryx_profitsharing_order(body)
                profitorder = OrderProfitSharing.objects.filter(order_id=order.id).first()
                if not profitorder:
                    profitorder, create = OrderProfitSharing.objects.get_or_create(
                        defaults={'order_id': order.id, "out_trade_no": out_trade_no}, order_id=order.id)
                if result['result_code'] == 'success':
                    order.is_profit_sharing = 4
                    order.save()
                    profitorder.profit_order_id = result['profit_order_id']
                    profitorder.profit_time = datetime.datetime.now()
                    profitorder.save()
                    OrderAdvance.objects.create(
                        order_id=order.id,
                        status=5,
                        remark='超管确认收货,解冻资金',
                        user_id=user.id
                    )
                    receipts_time = datetime.datetime.now().strftime('%Y-%m-%d')
                    bill = MerchantBill.objects.filter(receipts_time=receipts_time, merchant_id=order.merchant_id,
                                                       bill_type=order.order_species).first()
                    if not bill:
                        bill_data = {}
                        bill_data['merchant_id'] = order.merchant_id
                        bill_data['receipts_time'] = receipts_time
                        bill_data['amount'] = order.total_price
                        bill_data['commission'] = order.freight
                        bill_data['level_id'] = order.merchant.level_id
                        bill_data['payable_amount'] = order.total_price - order.freight
                        bill_data['channel_id'] = 3
                        bill_data['bill_type'] = order.order_species
                        bill, create = MerchantBill.objects.get_or_create(defaults=bill_data,
                                                                          receipts_time=receipts_time,
                                                                          merchant_id=order.merchant_id,
                                                                          bill_type=order.order_species)
                    else:
                        bill.amount = bill.amount + order.total_price
                        bill.commission = bill.commission + order.freight
                        bill.payable_amount = bill.payable_amount + order.total_price - order.freight
                        bill.save()
                    order.payable_settled_amount = order.total_price - order.freight
                    order.bill_id = bill.id
                    order.save()
                else:
                    order.is_profit_sharing = 4
                    order.save()
                    profitorder.error_msg = result['error_msg']
                    profitorder.profit_status = 2
                    profitorder.save()
                    OrderAdvance.objects.create(
                        order_id=order.id,
                        status=4,
                        remark='超管确认收货,解冻资金出错',
                        user_id=user.id
                    )
        except:
            pass
        return Response({'success': '操作成功'}, status=status.HTTP_200_OK)

    def update_orderaddress(self, request):
        order_id = self.request.data.get('order_id', None)
        province = self.request.data.get('province', None)
        city = self.request.data.get('city', None)
        district = self.request.data.get('district', None)
        mobile = self.request.data.get('mobile', None)
        receiver = self.request.data.get('receiver', None)
        place = self.request.data.get('place', None)
        if not order_id:
            return Response({'error': '请上传订单id'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            order = self.get_queryset().get(id=order_id)
            orderaddress = order.orderaddress
        except:
            return Response({'error': '无效订单id'}, status=status.HTTP_400_BAD_REQUEST)
        if order.order_type != 5:
            return Response({'error': '订单类型有误'}, status=status.HTTP_400_BAD_REQUEST)
        if order.state == 14:
            return Response({'error': '已完成订单不能修改配送地址'}, status=status.HTTP_400_BAD_REQUEST)
        if not all([province, city, district, mobile, receiver, place]):
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        orderaddress.province = province
        orderaddress.city = city
        orderaddress.district = district
        orderaddress.mobile = mobile
        orderaddress.receiver = receiver
        orderaddress.place = place
        orderaddress.save()
        return Response({'success': '操作成功'}, status=status.HTTP_200_OK)

    def use_coupon(self, request):
        user = self.request.iser
        order_id = self.request.data.get('order_id', None)
        if not all([order_id]):
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            order = OrderInfo.objects.get(id=order_id)
        except:
            return Response({'error': '无效订单号'}, status=status.HTTP_400_BAD_REQUEST)
        if order.state != 2:
            return Response({'error': '订单已无未核销商品,请刷新核实'}, status=status.HTTP_400_BAD_REQUEST)
        if order.order_type == 3:
            return Response({'error': '此核销码为兑换券类型,请在对应渠道使用'}, status=status.HTTP_400_BAD_REQUEST)
        goodscoupons_set = GoodsCoupons.objects.filter(order_id=order_id, status=1)
        for goodscoupons in goodscoupons_set:
            goodscoupons.status = 2
            goodscoupons.use_time = datetime.datetime.now()
            goodscoupons.use_user_id = user.id
            goodscoupons.save()
        data = {}
        goodscoupons_set = GoodsCoupons.objects.filter(order_id=order_id, status=1)
        if not goodscoupons_set:
            order.state = 3
            order.use_time = datetime.datetime.now()
            order.save()
            if order.order_type in [1, 7]:
                try:
                    if order.is_profit_sharing == 1:
                        out_trade_no = f'{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}{"%06d" % randint(0, 999999)}'
                        body = {
                            "program_id": "202316837141378884",
                            "shop_id": order.merchant.ruiyinxinMerchantID,
                            "order_id": out_trade_no,
                            "out_trade_no": order.orderNum,
                            "is_profit": "0"
                        }
                        result = ryx_profitsharing_order(body)
                        profitorder = OrderProfitSharing.objects.filter(order_id=order.id).first()
                        if not profitorder:
                            profitorder, create = OrderProfitSharing.objects.get_or_create(defaults={'order_id': order.id, "out_trade_no": out_trade_no}, order_id=order.id)
                        if result['result_code'] == 'success':
                            order.is_profit_sharing = 2
                            order.save()
                            profitorder.profit_order_id = result['profit_order_id']
                            profitorder.profit_time = datetime.datetime.now()
                            profitorder.save()
                            OrderAdvance.objects.create(
                                order_id=order.id,
                                status=5,
                                remark='管理员核销订单,解冻资金',
                                user_id=user.id
                            )
                            receipts_time = datetime.datetime.now().strftime('%Y-%m-%d')
                            bill = MerchantBill.objects.filter(receipts_time=receipts_time, merchant_id=order.merchant_id, bill_type=order.order_species).first()
                            if not bill:
                                bill_data = {}
                                bill_data['merchant_id'] = order.merchant_id
                                bill_data['receipts_time'] = receipts_time
                                bill_data['amount'] = order.total_price
                                bill_data['commission'] = order.freight
                                bill_data['level_id'] = order.merchant.level_id
                                bill_data['payable_amount'] = order.total_price - order.freight
                                bill_data['channel_id'] = 3
                                bill_data['bill_type'] = order.order_species
                                bill, create = MerchantBill.objects.get_or_create(defaults=bill_data, receipts_time=receipts_time, merchant_id=order.merchant_id, bill_type=order.order_species)
                            else:
                                bill.amount = bill.amount + order.total_price
                                bill.commission = bill.commission + order.freight
                                bill.payable_amount = bill.payable_amount + order.total_price - order.freight
                                bill.save()
                            order.payable_settled_amount = order.total_price - order.freight
                            order.bill_id = bill.id
                            order.save()
                        else:
                            order.is_profit_sharing = 2
                            order.save()
                            profitorder.error_msg = result['error_msg']
                            profitorder.profit_status = 2
                            profitorder.save()
                            OrderAdvance.objects.create(
                                order_id=order.id,
                                status=4,
                                remark='管理员核销订单,解冻资金出错',
                                user_id=user.id
                            )
                    elif order.is_profit_sharing == 3:
                        out_trade_no = f'{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}{"%06d" % randint(0, 999999)}'
                        body = {
                            "program_id": "202316837141378884",
                            "shop_id": order.merchant.ruiyinxinMerchantID,
                            "order_id": out_trade_no,
                            "out_trade_no": order.orderNum,
                            "is_profit": "2"
                        }
                        result = ryx_profitsharing_order(body)
                        profitorder = OrderProfitSharing.objects.filter(order_id=order.id).first()
                        if not profitorder:
                            profitorder, create = OrderProfitSharing.objects.get_or_create(defaults={'order_id': order.id, "out_trade_no": out_trade_no}, order_id=order.id)
                        if result['result_code'] == 'success':
                            order.is_profit_sharing = 4
                            order.save()
                            profitorder.profit_order_id = result['profit_order_id']
                            profitorder.profit_time = datetime.datetime.now()
                            profitorder.save()
                            OrderAdvance.objects.create(
                                order_id=order.id,
                                status=5,
                                remark='管理员核销订单,解冻资金',
                                user_id=user.id
                            )
                            receipts_time = datetime.datetime.now().strftime('%Y-%m-%d')
                            bill = MerchantBill.objects.filter(receipts_time=receipts_time, merchant_id=order.merchant_id, bill_type=order.order_species).first()
                            if not bill:
                                bill_data = {}
                                bill_data['merchant_id'] = order.merchant_id
                                bill_data['receipts_time'] = receipts_time
                                bill_data['amount'] = order.total_price
                                bill_data['commission'] = order.freight
                                bill_data['level_id'] = order.merchant.level_id
                                bill_data['payable_amount'] = order.total_price - order.freight
                                bill_data['channel_id'] = 3
                                bill_data['bill_type'] = order.order_species
                                bill, create = MerchantBill.objects.get_or_create(defaults=bill_data, receipts_time=receipts_time, merchant_id=order.merchant_id, bill_type=order.order_species)
                            else:
                                bill.amount = bill.amount + order.total_price
                                bill.commission = bill.commission + order.freight
                                bill.payable_amount = bill.payable_amount + order.total_price - order.freight
                                bill.save()
                            order.payable_settled_amount = order.total_price - order.freight
                            order.bill_id = bill.id
                            order.save()
                        else:
                            order.is_profit_sharing = 4
                            order.save()
                            profitorder.error_msg = result['error_msg']
                            profitorder.profit_status = 2
                            profitorder.save()
                            OrderAdvance.objects.create(
                                order_id=order.id,
                                status=4,
                                remark='管理员核销订单,解冻资金出错',
                                user_id=user.id
                            )
                except Exception as e:
                    logger.error(e)
                    pass
        return Response({'success': '操作成功'}, status=status.HTTP_200_OK)
        coupon_arr = []
        coupon_list = []
        for goodscoupons in goodscoupons_set:
            coupon_arr.append(goodscoupons.coupons_id)
            coupon_list.append({
                'goods_name': goodscoupons.goods.name,
                'price': goodscoupons.goods.price,
                'count': 1
            })
        data['request_date'] = {'coupon_arr': coupon_arr, 'order_id': order.id}
        data['coupon_list'] = coupon_list
        return Response(data, status=status.HTTP_200_OK)


class OrderInfoGROUPModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    filter_class = OrderInfoGROUPFilter

    def get_queryset(self):
        queryset = OrderInfoGROUP.objects.all()
        search = self.request.query_params.get('search', None)
        if search:
            es = Elasticsearch(["http://159.75.55.19:9200/"])
            body = {
                "query": {
                    "bool": {
                        "must": [],
                        "must_not": [],
                        "should": []
                    }
                },
                "from": 0,
                "size": 10000,
                "sort": ['_id'],
                "aggs": {}
            }
            if not search.isdigit():
                filter_data = {
                    "match_phrase_prefix": {
                        "text": search
                    }
                }
                body['query']['bool']['must'].append(filter_data)
            else:
                filter_data = {
                    "wildcard": {
                        "text": f'*{search}*'
                    }
                }
                body['query']['bool']['must'].append(filter_data)
            body['query']['bool']['must_not'].append({"wildcard": {"text": "*vm*"}})
            result = es.search(index="equity_mall_order", doc_type="modelresult", body=body)
            # return Response(result)
            hits = result['hits']['hits']
            id_list = []
            for order in hits:
                id_list.append(order['_id'])
            queryset = queryset.filter(order_id__in=id_list)
        return queryset.order_by('-id')

    def get_serializer_class(self):
        if self.action == 'list':
            return OrderInfoGROUPListModelSerializer
        else:
            return OrderInfoGROUPRetrieveModelSerializer

    def update(self, request, *args, **kwargs):
        instance = self.get_object()
        if instance.group_status != 1:
            return Response({'error': '拼团状态有误'}, status=status.HTTP_400_BAD_REQUEST)
        group_order_num = OrderInfo.objects.filter(state=15, group_id=instance.id).count()
        instance.group_order_num = group_order_num
        instance.save()
        group = OrderInfoGROUP.objects.get(id=instance.id)
        cancel_group_order(group)
        return Response({'sucess': '操作成功'}, status=status.HTTP_200_OK)


class OrderSchengleModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    # search_fields = ('merchant__name', 'ordergoods__goods__name', 'customer_user__mobile')
    filter_class = OrderSchengleFilter

    def get_serializer_class(self):
        if self.action == 'list':
            return OrderSchengleListModelSerializer
        else:
            return OrderSchengleRetrieveModelSerializer

    def get_queryset(self):
        search = self.request.query_params.get('search', None)
        queryset = OrderSchengle.objects.all()
        if search:
            es = Elasticsearch(["http://159.75.55.19:9200/"])
            body = {
                "query": {
                    "bool": {
                        "must": [],
                        "must_not": [],
                        "should": []
                    }
                },
                "from": 0,
                "size": 10000,
                "sort": [{"_id": "asc"}],
                "aggs": {}
            }
            if not search.isdigit():
                filter_data = {
                    "match_phrase_prefix": {
                        "text": search
                    }
                }
                body['query']['bool']['must'].append(filter_data)
            else:
                filter_data = {
                    "wildcard": {
                        "text": f'*{search}*'
                    }
                }
                body['query']['bool']['must'].append(filter_data)
            filter_data = {
                "wildcard": {
                    "text": '6013315788'
                }
            }
            body['query']['bool']['should'].append(filter_data)
            result = es.search(index="equity_mall_order", doc_type="modelresult", body=body)
            # print(result)
            hits = result['hits']['hits']
            id_list = []
            for order in hits:
                id_list.append(order['_id'])
            queryset = queryset.filter(Q(order_id__in=id_list) | Q(orderId__contains=search))
        return queryset.order_by('-id')

    def destroy(self, request, *args, **kwargs):
        instance = self.get_object()
        user = self.request.iser
        remark = self.request.data.get('remark', None)
        if user.id not in [1, 12105, 9297, 65139]:
            return Response({'error': '您无权限操作'}, status=status.HTTP_400_BAD_REQUEST)
        if instance.order_state != 2:
            return Response({'error': '活动状态有误'}, status=status.HTTP_400_BAD_REQUEST)
        today = datetime.datetime.now().strftime("%Y-%m-%d")
        if instance.create_time.strftime("%Y-%m-%d") != today:
            return Response({'error': '订单已过了退款时效,不能操作'}, status=status.HTTP_400_BAD_REQUEST)
        data = {}
        data['source'] = 'GDYZQY'
        data['orderId'] = instance.orderId
        result = schengle.cancel_order(data)
        if result['resultCode'] != 'success':
            return Response({'error': f'取消订单失败,{result["resultDesc"]}'}, status=status.HTTP_400_BAD_REQUEST)
        instance.order_state = 6
        instance.cancel_time = datetime.datetime.now()
        instance.cancel_user_id = user.id
        instance.save()
        order = instance.order
        url = 'https://api.gdwxyf.com/api/refund.do'
        datetime_now = datetime.datetime.now()
        order_id = f'{datetime_now.strftime("%Y%m%d%H%M%S")}{"%06d" % randint(0, 999999)}'
        body = {
            "program_id": "202316837141378884",
            "shop_id": order.merchant.ruiyinxinMerchantID,
            "refund_order_id": order_id,
            "order_id": order.orderNum
        }
        key = '6715C7D4435343BA0459EAEC2334D81B'
        sign = get_sign(body, key)
        body['sign'] = sign
        response = requests.post(url=url, json=body)
        result = response.json()
        if result['result_code'] == 'success':
            refund_order_data = {}
            refund_order_data['order_id'] = order.id
            refund_order_data['user_id'] = user.id
            refund_order_data['operator_role'] = user.role_id
            refund_order_data['refund_order_id'] = order_id
            refund_order_data['refund_transaction_id'] = result['refund_transaction_id']
            refund_order_data['plat_refund_order_id'] = result['plat_refund_order_id']
            refund_order_data['refund_money'] = result['refund_money']
            refund_order_data['refund_procedure_money'] = result['commission']
            refund_order_data['refund_time'] = datetime.datetime.strptime(result['refund_end_time'], '%Y%m%d%H%M%S')
            refund_order_data['remark'] = remark
            RefundOrderInfo.objects.create(**refund_order_data)
            order.state = 8
            order.refund_money = order.realpay_amount
            order.out_refund_no = order_id
            order.refund_id = result['refund_transaction_id']
            order.save()
            try:
                after_sale = AfterSaleApplicationForm.objects.filter(order_id=order.id, form_status=1).last()
                if after_sale:
                    after_sale.form_status = 2
                    after_sale.refund_time = datetime.datetime.strptime(result['refund_end_time'], '%Y%m%d%H%M%S')
                    after_sale.save()
            except:
                pass
            bill = order.bill
            bill.amount = bill.amount - order.total_price
            bill.commission = bill.commission - order.freight
            bill.payable_amount = bill.payable_amount - order.total_price + order.freight
            bill.save()
            order.payable_settled_amount = 0
            order.bill_id = None
            order.save()
            ordergoods_set = order.ordergoods_set.all()
            for ordergoods in ordergoods_set:
                goods = ordergoods.goods
                launched_goods = ordergoods.launched_goods
                count = ordergoods.count
                origin_sales = goods.sales
                origin_remain_stock = goods.remain_stock
                new_sales = origin_sales - count
                new_remain_stock = origin_remain_stock + count
                goods.sales = new_sales
                goods.remain_stock = new_remain_stock
                goods.save()
                if launched_goods:
                    launched_origin_stock = launched_goods.stock
                    launched_origin_sales = launched_goods.sales
                    launched_new_stock = launched_origin_stock + count
                    launched_new_sales = launched_origin_sales - count
                    launched_goods.stock = launched_new_stock
                    launched_goods.sales = launched_new_sales
                    launched_goods.save()
                else:
                    origin_stock = goods.stock
                    new_stock = origin_stock + count
                    goods.stock = new_stock
                    goods.save()
            return Response({'success': '退款成功'}, status=status.HTTP_200_OK)
        else:
            refund_order_data = {}
            refund_order_data['refund_status'] = 2
            refund_order_data['operator_role'] = user.role_id
            refund_order_data['order_id'] = order.id
            refund_order_data['user_id'] = user.id
            refund_order_data['return_msg'] = result['error_msg']
            refund_order_data['refund_order_id'] = order_id
            refund_order_data['refund_time'] = datetime_now
            refund_order_data['remark'] = remark
            RefundOrderInfo.objects.create(**refund_order_data)
            return Response({'error': f'取消订单成功,退款失败,原因:{result["error_msg"]}'}, status=status.HTTP_400_BAD_REQUEST)


class OrderAdvanceModelViewSet(ModelViewSet):

    queryset = OrderAdvance.objects.all().order_by('-id')
    serializer_class = OrderAdvanceModelSerializer
    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    filter_class = OrderAdvanceFilter


class YSHAreaCodeModelViewSet(ModelViewSet):

    queryset =  YSHAreaCode.objects.all().order_by('id')
    serializer_class = YSHAreaCodeModelSerializer
    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('name',)


class YSHContractModelViewSet(ModelViewSet):

    queryset =  YSHContract.objects.filter(is_delete=0)
    serializer_class = YSHContractModelSerializer
    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('supplierName',)


class ExpressModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    filter_class = ExpressFilter
    search_fields = ('express_number',)

    def get_queryset(self):
        queryset = Express.objects.all().order_by('-id')
        return queryset

    def get_serializer_class(self):
        if self.action == 'list':
            return ExpressListModelSerializer
        else:
            return ExpressRetrieveModelSerializer

    def export_express(self, request):
        user = self.request.iser
        level = user.level
        deliverystatus = self.request.query_params.get('deliverystatus', None)
        create_time_begin = self.request.query_params.get('create_time_begin', None)
        create_time_end = self.request.query_params.get('create_time_end', None)
        sign_time_begin = self.request.query_params.get('sign_time_begin', None)
        sign_time_end = self.request.query_params.get('sign_time_end', None)
        search = self.request.query_params.get('search', None)
        condition = 'WHERE id>9'
        if search:
            condition = condition + f' AND express_number LIKE "%{search}%"'
        if create_time_begin:
            condition = condition + f' AND create_time >= "{create_time_begin + " 00:00:00"}"'
        if create_time_end:
            condition = condition + f' AND create_time <= "{create_time_end + " 23:59:59"}"'
        if sign_time_begin:
            condition = condition + f' AND sign_time >= "{sign_time_begin + " 00:00:00"}"'
        if sign_time_end:
            condition = condition + f' AND sign_time <= "{sign_time_end + " 23:59:59"}"'
        if deliverystatus:
            condition = condition + f' AND deliverystatus IN ({deliverystatus})'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT c.*,d.express_type_name FROM (SELECT a.*,b.order_id FROM (SELECT id,express_number,deliverystatus,express_type_id,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S') AS create_time,DATE_FORMAT(sign_time,'%Y-%m-%d %H:%i:%S') AS sign_time FROM tb_order_express {condition})AS a LEFT JOIN (SELECT express_id,GROUP_CONCAT(order_id)AS order_id FROM tb_order_express_relevance GROUP BY express_id)AS b ON a.id=b.express_id)AS c LEFT JOIN (SELECT id,express_type_name FROM tb_order_express_type)AS d ON c.express_type_id=d.id;
            '''
            cursor.execute(sql)
        except Exception as e:
            sql = f'''
            SELECT c.*,d.express_type_name FROM (SELECT a.*,b.order_id FROM (SELECT id,express_number,deliverystatus,express_type_id,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S') AS create_time,DATE_FORMAT(sign_time,'%Y-%m-%d %H:%i:%S') AS sign_time FROM tb_order_express)AS a LEFT JOIN (SELECT express_id,GROUP_CONCAT(order_id)AS order_id FROM tb_order_express_relevance GROUP BY express_id)AS b ON a.id=b.express_id)AS c LEFT JOIN (SELECT id,express_type_name FROM tb_order_express_type)AS d ON c.express_type_id=d.id;
            '''
            cursor.execute(sql)
        ziduan = ['id', '快递公司', '快递单号', '快递状态', '录入时间', '签收时间', '订单id']
        cols = ['id', 'express_type_name', 'express_number', 'deliverystatus', 'create_time', 'sign_time', 'order_id']
        response = StreamingHttpResponse(self.get_express_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('快递信息.csv'))
        return response

    def get_express_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        DELIVERYSTATUS_ENUM = {
            0: '在途',
            1: '揽收',
            2: '疑难',
            3: '已签收',
            4: '签退',
            5: '派件',
            6: '退回',
            7: '转投',
            8: '清关',
            9: '错误',
            14: '拒签'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'deliverystatus':
                    tmp_str += '"%s",' % (DELIVERYSTATUS_ENUM[row[col]])
                elif col in ['express_number']:
                    tmp_str += '\'"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk').replace('\n', '') if row[col] else '')
                elif col in ['order_id']:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk').replace(',', '，') if row[col] else '')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class AfterSaleApplicationFormModelViewSet(ModelViewSet):

    pagination_class = MyPage

    def get_queryset(self):
        user = self.request.iser
        queryset = AfterSaleApplicationForm.objects.filter().exclude(form_status=4)
        return queryset

    def get_serializer_class(self):
        return AfterSaleApplicationFormModelSerializer

    def update(self, request, *args, **kwargs):
        # user = self.request.iser
        # instance = self.get_object()
        # if instance.form_status != 1:
        #     return Response({'error': '申请单状态有误'}, status=status.HTTP_400_BAD_REQUEST)
        # audit_status = self.request.data.get('audit_status', None)
        # return_msg = self.request.data.get('return_msg', None)
        # order = instance.order
        # if order.state != 5:
        #     return Response({'error': '订单单状态有误'}, status=status.HTTP_400_BAD_REQUEST)
        # if not audit_status:
        #     return Response({'error': '请上传审核参数'}, status=status.HTTP_400_BAD_REQUEST)
        # if str(audit_status) == '2':
        #     url = 'https://api.gdwxyf.com/api/refund.do'
        #     datetime_now = datetime.datetime.now()
        #     order_id = f'{datetime_now.strftime("%Y%m%d%H%M%S")}{"%06d" % randint(0, 999999)}'
        #     body = {
        #         "program_id": "202316837141378884",
        #         "shop_id": order.merchant.ruiyinxinMerchantID,
        #         "refund_order_id": order_id,
        #         "order_id": order.orderNum
        #     }
        #     key = '6715C7D4435343BA0459EAEC2334D81B'
        #     sign = get_sign(body, key)
        #     body['sign'] = sign
        #     response = requests.post(url=url, json=body)
        #     result = response.json()
        #     if result['result_code'] == 'success':
        #         refund_order_data = {}
        #         refund_order_data['order_id'] = order.id
        #         refund_order_data['user_id'] = user.id
        #         refund_order_data['operator_role'] = 1
        #         refund_order_data['refund_order_id'] = order_id
        #         refund_order_data['refund_transaction_id'] = result['refund_transaction_id']
        #         refund_order_data['plat_refund_order_id'] = result['plat_refund_order_id']
        #         refund_order_data['refund_money'] = result['refund_money']
        #         refund_order_data['refund_procedure_money'] = result['commission']
        #         refund_order_data['refund_time'] = datetime.datetime.strptime(result['refund_end_time'], '%Y%m%d%H%M%S')
        #         refund_order_data['remark'] = '商户同意退款'
        #         RefundOrderInfo.objects.create(**refund_order_data)
        #         order.state = 8
        #         # order.refund_money += Decimal(result['refund_money'])
        #         order.refund_money = order.realpay_amount
        #         order.out_refund_no = order_id
        #         order.refund_id = result['refund_transaction_id']
        #         order.save()
        #         instance.form_status = 2
        #         # instance.refund_money = result['refund_money']
        #         instance.refund_time = datetime.datetime.strptime(result['refund_end_time'], '%Y%m%d%H%M%S')
        #         instance.save()
        #         try:
        #             aftersale_applyment_send.delay({'id': instance.id})
        #         except:
        #             pass
        #         return Response({'success': '退款成功'}, status=status.HTTP_200_OK)
        #     else:
        #         refund_order_data = {}
        #         refund_order_data['refund_status'] = 2
        #         refund_order_data['operator_role'] = 1
        #         refund_order_data['order_id'] = order.id
        #         refund_order_data['user_id'] = user.id
        #         refund_order_data['return_msg'] = result['error_msg']
        #         refund_order_data['refund_order_id'] = order_id
        #         refund_order_data['refund_time'] = datetime_now
        #         refund_order_data['remark'] = '商户同意退款'
        #         RefundOrderInfo.objects.create(**refund_order_data)
        #         instance.form_status = 3
        #         instance.return_msg = result['error_msg']
        #         instance.save()
        #         try:
        #             aftersale_applyment_send.delay({'id': instance.id})
        #         except:
        #             pass
        #         return Response({'error': f'退款失败,原因:{result["error_msg"]}'}, status=status.HTTP_400_BAD_REQUEST)
        # else:
        #     if not return_msg:
        #         return Response({'error': '请写明拒绝退款原因'}, status=status.HTTP_400_BAD_REQUEST)
        #     order.state = instance.order_status
        #     order.save()
        #     instance.form_status = 3
        #     instance.return_msg = return_msg
        #     instance.save()
        #     try:
        #         aftersale_applyment_send.delay({'id': instance.id})
        #     except:
        #         pass
        #     return Response({'success': '操作成功'}, status=status.HTTP_200_OK)
        user = self.request.iser
        instance = self.get_object()
        if instance.form_status not in [1, 8]:
            return Response({'error': '申请单状态有误'}, status=status.HTTP_400_BAD_REQUEST)
        audit_status = self.request.data.get('audit_status', None)
        return_msg = self.request.data.get('return_msg', None)
        order = instance.order
        if order.state != 5:
            return Response({'error': '订单状态有误'}, status=status.HTTP_400_BAD_REQUEST)
        if not audit_status:
            return Response({'error': '请上传审核参数'}, status=status.HTTP_400_BAD_REQUEST)
        if instance.form_status == 8:
            if instance.reason_type == 1:
                if str(audit_status) == '2':
                    url = 'https://api.gdwxyf.com/api/refund.do'
                    datetime_now = datetime.datetime.now()
                    order_id = f'{datetime_now.strftime("%Y%m%d%H%M%S")}{"%06d" % randint(0, 999999)}'
                    body = {
                        "program_id": "202316837141378884",
                        "shop_id": order.merchant.ruiyinxinMerchantID,
                        "refund_order_id": order_id,
                        "order_id": order.orderNum
                    }
                    key = '6715C7D4435343BA0459EAEC2334D81B'
                    sign = get_sign(body, key)
                    body['sign'] = sign
                    response = requests.post(url=url, json=body)
                    result = response.json()
                    if result['result_code'] == 'success':
                        refund_order_data = {}
                        refund_order_data['order_id'] = order.id
                        refund_order_data['user_id'] = user.id
                        refund_order_data['operator_role'] = 4
                        refund_order_data['refund_order_id'] = order_id
                        refund_order_data['refund_transaction_id'] = result['refund_transaction_id']
                        refund_order_data['plat_refund_order_id'] = result['plat_refund_order_id']
                        refund_order_data['refund_money'] = result['refund_money']
                        refund_order_data['refund_procedure_money'] = result['commission']
                        refund_order_data['refund_time'] = datetime.datetime.strptime(result['refund_end_time'], '%Y%m%d%H%M%S')
                        refund_order_data['remark'] = '管理员同意退款'
                        RefundOrderInfo.objects.create(**refund_order_data)
                        order.state = 8
                        # order.refund_money += Decimal(result['refund_money'])
                        order.refund_money = order.realpay_amount
                        order.out_refund_no = order_id
                        order.refund_id = result['refund_transaction_id']
                        order.save()
                        instance.form_status = 2
                        # instance.refund_money = result['refund_money']
                        instance.refund_time = datetime.datetime.strptime(result['refund_end_time'], '%Y%m%d%H%M%S')
                        instance.save()
                        return Response({'success': '退款成功'}, status=status.HTTP_200_OK)
                    else:
                        refund_order_data = {}
                        refund_order_data['refund_status'] = 2
                        refund_order_data['operator_role'] = 4
                        refund_order_data['order_id'] = order.id
                        refund_order_data['user_id'] = user.id
                        refund_order_data['return_msg'] = result['error_msg']
                        refund_order_data['refund_order_id'] = order_id
                        refund_order_data['refund_time'] = datetime_now
                        refund_order_data['remark'] = '管理员同意退款'
                        RefundOrderInfo.objects.create(**refund_order_data)
                        instance.form_status = 3
                        instance.return_msg = result['error_msg']
                        instance.save()
                        return Response({'error': f'退款失败,原因:{result["error_msg"]}'}, status=status.HTTP_400_BAD_REQUEST)
                else:
                    if not return_msg:
                        return Response({'error': '请写明拒绝退款原因'}, status=status.HTTP_400_BAD_REQUEST)
                    order.state = instance.order_status
                    order.save()
                    instance.form_status = 11
                    instance.return_msg = return_msg
                    instance.save()
                    OrderAdvance.objects.create(
                        order_id=instance.order_id,
                        status=7,
                        remark='管理员驳回申请',
                        user_id=user.id
                    )
                    return Response({'success': '操作成功'}, status=status.HTTP_200_OK)
            else:
                if str(audit_status) == '2':
                    expired_time = datetime.datetime.now() + datetime.timedelta(days=3)
                    instance.form_status = 5
                    instance.expired_time = expired_time
                    instance.save()
                    return Response({'success': '操作成功'}, status=status.HTTP_200_OK)
                else:
                    if not return_msg:
                        return Response({'error': '请写明拒绝退款原因'}, status=status.HTTP_400_BAD_REQUEST)
                    order.state = instance.order_status
                    order.save()
                    instance.form_status = 11
                    instance.return_msg = return_msg
                    instance.save()
                    OrderAdvance.objects.create(
                        order_id=instance.order_id,
                        status=7,
                        remark='管理员驳回申请',
                        user_id=user.id
                    )
                    return Response({'success': '操作成功'}, status=status.HTTP_200_OK)
        else:
            if str(audit_status) == '2':
                url = 'https://api.gdwxyf.com/api/refund.do'
                datetime_now = datetime.datetime.now()
                order_id = f'{datetime_now.strftime("%Y%m%d%H%M%S")}{"%06d" % randint(0, 999999)}'
                body = {
                    "program_id": "202316837141378884",
                    "shop_id": order.merchant.ruiyinxinMerchantID,
                    "refund_order_id": order_id,
                    "order_id": order.orderNum
                }
                key = '6715C7D4435343BA0459EAEC2334D81B'
                sign = get_sign(body, key)
                body['sign'] = sign
                response = requests.post(url=url, json=body)
                result = response.json()
                if result['result_code'] == 'success':
                    refund_order_data = {}
                    refund_order_data['order_id'] = order.id
                    refund_order_data['user_id'] = user.id
                    refund_order_data['operator_role'] = 4
                    refund_order_data['refund_order_id'] = order_id
                    refund_order_data['refund_transaction_id'] = result['refund_transaction_id']
                    refund_order_data['plat_refund_order_id'] = result['plat_refund_order_id']
                    refund_order_data['refund_money'] = result['refund_money']
                    refund_order_data['refund_procedure_money'] = result['commission']
                    refund_order_data['refund_time'] = datetime.datetime.strptime(result['refund_end_time'], '%Y%m%d%H%M%S')
                    refund_order_data['remark'] = '管理员同意退款'
                    RefundOrderInfo.objects.create(**refund_order_data)
                    order.state = 8
                    # order.refund_money += Decimal(result['refund_money'])
                    order.refund_money = order.realpay_amount
                    order.out_refund_no = order_id
                    order.refund_id = result['refund_transaction_id']
                    order.save()
                    instance.form_status = 2
                    # instance.refund_money = result['refund_money']
                    instance.refund_time = datetime.datetime.strptime(result['refund_end_time'], '%Y%m%d%H%M%S')
                    instance.save()
                    return Response({'success': '退款成功'}, status=status.HTTP_200_OK)
                else:
                    refund_order_data = {}
                    refund_order_data['refund_status'] = 2
                    refund_order_data['operator_role'] = 4
                    refund_order_data['order_id'] = order.id
                    refund_order_data['user_id'] = user.id
                    refund_order_data['return_msg'] = result['error_msg']
                    refund_order_data['refund_order_id'] = order_id
                    refund_order_data['refund_time'] = datetime_now
                    refund_order_data['remark'] = '商户同意退款'
                    RefundOrderInfo.objects.create(**refund_order_data)
                    instance.form_status = 3
                    instance.return_msg = result['error_msg']
                    instance.save()
                    return Response({'error': f'退款失败,原因:{result["error_msg"]}'}, status=status.HTTP_400_BAD_REQUEST)
            else:
                if not return_msg:
                    return Response({'error': '请写明拒绝退款原因'}, status=status.HTTP_400_BAD_REQUEST)
                order.state = instance.order_status
                order.save()
                instance.form_status = 11
                instance.return_msg = return_msg
                instance.save()
                OrderAdvance.objects.create(
                    order_id=instance.order_id,
                    status=7,
                    remark='管理员驳回申请',
                    user_id=user.id
                )
                return Response({'success': '操作成功'}, status=status.HTTP_200_OK)


class ExpressQuestModelViewSet(ModelViewSet):

    pagination_class = MyPage
    # filter_class = UpdateStockUsableMerchantFilter
    # filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    # search_fields = ('customer_user__mobile', )
    queryset = ExpressQuest.objects.all().order_by('-id')

    def get_serializer_class(self):
        return ExpressQuestListModelSerializer


class ExpressTypeModelViewSet(ModelViewSet):

    queryset = ExpressType.objects.all()
    serializer_class = ExpressTypeModelSerializer

    def choices_expresstype(self, request):
        express_number = self.request.query_params.get('express_number', '')
        url = f'https://alayn.baidu.com/express/appdetail/get_com?num={express_number}'
        response = requests.get(url=url)
        try:
            result = response.json()
            express_type = ExpressType.objects.filter(express_type=result['data']['company']).first()
            if express_type:
                return Response(ExpressTypeModelSerializer(express_type).data, status=status.HTTP_200_OK)
            else:
                return Response({}, status=status.HTTP_200_OK)
        except:
            return Response({}, status=status.HTTP_200_OK)

    def syn_express(self, request):
        express_id = self.request.query_params.get('express_id', None)
        try:
            express = Express.objects.get(id=express_id)
        except:
            return Response({'error': '无效物流id'}, status=status.HTTP_400_BAD_REQUEST)
        if express.deliverystatus not in [0, 1, 5, 2, 6]:
            return Response({'error': '操作成功'}, status=status.HTTP_200_OK)
        try:
            order = ExpressOrderRelevance.objects.filter(express_id=express.id).first()
            phone = OrderAddress.objects.filter(order_id=order.order_id).first()
            if phone:
                result = KuaiDi100().track(express.express_type.express_type, express.express_number, phone.mobile[-4:])
            else:
                result = KuaiDi100().track(express.express_type.express_type, express.express_number)
            if result['message'] != 'ok':
                return Response({'error': result['message']}, status=status.HTTP_400_BAD_REQUEST)
            if result['status'] == '200':
                express.deliverystatus = result['state']
                express.save()
                for i in result['data']:
                    plan_data = {}
                    plan_data['express_id'] = express.id
                    plan_data['create_time'] = i['time']
                    plan_data['remark'] = i['context']
                    ExpressPlan.objects.get_or_create(defaults=plan_data, create_time=i['time'], express_id=express.id)
                if str(express.deliverystatus) == "3":
                    try:
                        sign_time = ExpressPlan.objects.filter(express_id=express.id).aggregate(Max('create_time'))['create_time__max']
                        express.sign_time = sign_time
                        express.save()
                    except:
                        pass
            else:
                express.deliverystatus = 9
                express.save()
                ExpressPlan.objects.create(remark=result['message'], create_time=datetime.datetime.now(), express_id=express.id)
        except Exception as e:
            print(e)
            return Response({'error': '操作失败,请联系管理员'}, status=status.HTTP_400_BAD_REQUEST)
        return Response({'success': '操作成功'}, status=status.HTTP_200_OK)